Adding date range to SumIf/CountIfs combination

You won't be able to enter this array formula in merged cell D12. In D13 array-enter this formula.

Formula:

=SUM(IFERROR((Sheet2!$A$2:$A$661>=Master!$F$3)*(Sheet2!$A$2:$A$661<=Master!$J$3)*(Sheet2!$F$2:$F$661="ABC")*(IF(FREQUENCY(Sheet2!$E$2:$E$661,Sheet2!$E$2:$E$661),1,0)),0))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Been there ... done that. Glad it works. Thanks for the feedback and the rep.

I have to leave for a while, but at a glance the range reference rows are unequal. They need to be. Secondly it is rarely a good idea to reference whole columns / rows. It makes Excel work much too hard. It has to calculate, evaluate or search over a million rows needlessly. It is best to choose a range reference with an upper boundary that you think will modestly exceed anticipated need so formulas don't have to continuously be edited (blechh!).

Another way to do that is with Dynamic Named Ranges DNRs. These are formula driven and defined in Name Manager. They shrink and grow automatically with your data. It's a topic on its own, and if you are interested query the forum for Dynamic Named Ranges and you will find no shortage of instructions on how to build them.

Thirdly I don't understand how INDIRECT is being used here or if it is necessary. Without an additional upload to see what the formula is trying to do with that in context I can only guess. I'm a bad guesser.

I'll be back. In the meantime perhaps someone else will have some suggestions. It's good to see one attempting this on their own. That's ambitious. It's always fun to explore and learn.

Originally Posted by lans4rd

Thank you for the reply. I'll look up the information on Named Ranges to see if I can figure it out. Appreciate it.

...and not just fixed range Named ranges. Make sure you check out DYNAMIC Named Ranges too.