# SUMIFS with Date range and Category column

I am newbie in using Excel functions. I have an excel file as shown in the following screenshot. I used following formula for getting total sale amounts for the sale happened between 02/01/2019 and 06/30/2019. It works fine

=SUMIFS(D2:D6,C2:C6,">=" & F3, C2:C6,"<=" & G3)

I need to improve the logic to get total same amount for each category for the sale happened between 02/01/2019 and 06/30/2019. How to do it?

Note: I need to make this as a utility where other users will just update the values in column `F3` and `G3`. So, I cannot use `PIVOT` table.

## 2 Answers

make a list of unique categories in F6 downward. Then in G6 use your formula with an additional criteria:

``````=SUMIFS(\$D\$2:\$D\$6,\$C\$2:\$C\$6,">=" & \$F\$3, \$C\$2:\$C\$6,"<=" & \$G\$3,\$A\$2:\$A\$6,\$F6)
``````

Note the addition of the \$. This will keep the cell references from changing as you copy the cell downward. note you do not want to lock the F6 row reference since you want it to change as you copy down.

To get the Sale Total based on two criteria, Category & Date, you need to follow these steps. How it works:

• An array formula in Cell `F29` to get the Unique list of Category:

``````=IFERROR(INDEX(\$A\$29:\$A\$35, MATCH(0,COUNTIF(\$F\$28:F28, \$A\$29:\$A\$35), 0)),"")
``````

N.B. Finish formula with Ctrl+Shift+Enter then fill it down.

• Enter this for formula in Cell `G29` & fill it down.

``````=SUMPRODUCT((\$A\$29:\$A\$35=\$F29)*(\$C\$29:\$C\$35>=\$H\$28),(\$C\$29:\$C\$35<=\$I\$28)*(\$D\$29:\$D\$35))
``````

Adjust criteria Date & cell references in the Formula as needed.