In this tutorial we will learn about Advanced SUMIFS for Date Ranges
Example:
Here we have a data in which there are 3 columns consists of Date, Customer Id and Amount
Now we have picked random customer ID from the data, and we want to find out Customer ID wise total value and count of transactions between two dates. So here three Criteria will be made:
1. Crtiera1 – Customer ID
2. Criteria2 – Start Date
3. Criteria 3 – End Date
Now in order to find out the total value and count of transactions between two dates then we will follow the steps given below:
1. Write =SUMIFS and press the Tab Key
2. Then press on fx button or press the Shortcut key Ctrl + A
3. A Function Panel Arguments Panel will open
Note – After opening the panel. Press the Tab Key to open new Boxes in the panel
4. In Criteria1 select the Customer ID cell
5. In Criteria 2 write “>=”& and join it by selecting Start Date
6. Press the Tab key twice to open the Criteria 3 box
7. In Criteria 3 write “<=”& and join it by selecting End Date
8. In Crtieria_range1 choose the Customer ID column and press the Tab key
9. In Criteria_range2 choose the date column and press the Tab key
10. Again in Criteria_range3 select the same date which you have chosen in Crtieria_range2 and press the Tab key
11. Choose the amount column as Sum Range and press the Tab key
12. Press Ok and copy paste the answer to the bottom to get the next answer
Also if you want to calculate the number of transactions between 3rd April 14 to 8th April 14 then follow the steps given below:
1. Copy the SUMIF formula parameters by pressing the Shortcut Key Ctrl + C
2. Then write =COUNTIFS and press the Tab key
3. Paste the SUMIF Formula Parameters by pressing the Shortcut Key Ctrl + V
4. Press Enter to see the number of transactions
