COUNTIF Formulas with 48 different variations - simplify needed!

[CD2017]

Hello, I need to pull together a table of statistics in excel. I've used 48 COUNTIFS to count the entries I need but I was hoping somebody might be able to advise me how to do this with a more simplified formula.

The formula is: =COUNTIFS(Report!D:D,C5,Report!B:B,$D$4,Report!N:N,Trans_Description!$B$3)+COUNTIFS(Report!D:D,C5,Report!B:B,$D$4,Report!N:N,Trans_Description!$B$3)+COUNTIFS(Report!D:D,C5,Report!B:B,$D$4,Report!N:N,Trans_Description!$B$4)+.....COUNTIFS(Report!D:D,C5,Report!B:B,$D$4,Report!N:N,Trans_Description!$B$49)

The third criteria will be within this range: Trans_Description!$B$3:Trans_Description!$B$49

Is there a way I can specify a range of values as one of the criteria?

Any help would be appreciated!

 

[FormR]

Is there a way I can specify a range of values as one of the criteria?

Hi, you can try like this:

=SUMPRODUCT(COUNTIFS(Report!D:D,C5,Report!B:B,$D$4,Report!N:N,Trans_Description!$B$3:$B$49))

 

[CD2017]

Hi, you can try like this:

=SUMPRODUCT(COUNTIFS(Report!D:D,C5,Report!B:B,$D$4,Report!N:N,Trans_Description!$B$3:$B$49))

Excellent - thank you!

 

[CD2017]

Excellent - thank you!

Hello again, is there a similar way to do this with SUMIFS formula?

The formula I have is:

=SUMIFS('mySS report_A'!G:G,'mySS report_A'!B:B,D29,'mySS report_A'!D:D,C30,'mySS report_A'!S:S,"USD",'mySS report_A'!N:N,Trans_Description_A!B3:B49)

Thanks!

 

[FormR]

=SUMPRODUCT(SUMIFS('mySS report_A'!G:G,'mySS report_A'!B:B,D29,'mySS report_A'!D:D,C30,'mySS report_A'!S:S,"USD",'mySS report_A'!N:N,Trans_Description_A!B3:B49))

Hi, try the additions in red above.