Hello there everybody in this particular session we will see how some ifs can be used to incorporate dates as input as criteria. And before us lies a very small data hypothetical data 15 days of sales made by different made to different customers and these are the amount. Now what we want to start off with is the two inputs which are marked in yellow star did an end date. So, between a certain start date which could be third of April, one of the dates in the given range and ending date could be 10th of April between those two dates both included Give me the amount, amount of sales that has happened. In the next phase we will also add the third variable wherein we will ask specifically for a certain customer ID code between a certain start and till end date, what is the amount of sales that has happened?
Now, this is quite often used in In preparing dashboards, where ultimately the end user has a drop down list from where he might choose a customer ID, and if he changes the start and end date, the amount changes. So let me give dummy data let's say third APR, and writing APR to clarify to excel that this is APR that I'm catering to 2014, Enter. And indeed, let me put it 11th APR 2014, enter. Now under the sell of amount, let me go ahead and write some ifs formula. I'm going to click on the FX button. Now once I reach out to the function argument approach, Let me press TAB key TAB key multiple times, so that I get to see all the available text boxes for parameters.
Now, the trick The trick is we start with criteria one, and we will put a double quotation more than equal to double quotation close. Now this is the operator which will define more than or equal to more than or equal to means starting date onwards. So I put an ampersand sign to combine this operator with that of date, and I choose a cell containing that date. So, be careful about the placement of the double quotation, the double quotation must not end after IE six, this would be incorrect. First, the operator operator completed double quotation closed, then ampersand and then the cell containing the date. Now, let me press Shift Tab key or else let me click on the second text box here in we'll be talking about the range in which these dates can be found.
So, I might choose the entire column A, and if I choose the entire column A, please keep in mind next time, if you have to choose any range, you will have to choose the entire range in there. In that case to or else you may only choose a certain range of values restricted in either case will be consistent. So I choose column A, I fix it. So one pair competed Now let me step inside criteria to that's the fifth box. Similar as the criteria one, I put double quotation less than or equal to double quotation closed ampersand ampersand so as to combine that operator with the cell input end date. And where would I find this criteria range has to be column A once again.
So two criteria here already provided, let me step inside some range. And that's where I choose column D, full range, I press f4. Notice the answer 4100 will verify the accuracy is shortly. At this moment, Let me press OK. It gave me 4100 between third and 14th April, let me select all these cells. In fact, let me prepare a quick sum formula which is going to be manual.
So between 30 and 40 in April, let me see the answer. Answer 4100 will also try to check the accuracy of the same using a basic sum formula. Third April till 11th April, enter, there you go 4100. And the best part is the data need not be arranged chronologically. For example, in the last case if I write 10th APR 2014 summit formula will automatically pick up that 300 hence the answer becoming 4400. Let me press Ctrl Z.
And now we will add one more variable so I'm going to put one of this customer ID codes copy and paste here. inside the cell where I have written the formula, let me click back on effects. The same old parameters on board but this time let me scroll down and access the third criteria. Criteria number three, and let me choose the cell containing customer ID. That's criteria three, criteria range three shall be the entire column. Which I'm going to fix right now.
Okay, fine. So four double 7072 how many occurrences of the same between third and 11th April, let me quickly find out, I could have also used a filter, but since the data is small, I'm doing so manually. So between fourth as third and 11th 11th April. So between third and 11th April, I have four values. Let me simply use some some formula. First one, comma, second, comma three, comma four, enter.
There you go. 2200. So some IDs can be used with up to 127 criteria, along with dates or numbers or text. As we go ahead and learn counters, we'll also see how to populate number of transactions against that particular criteria.