Alternative to writing COUNTIFS 40+ times

Alternative to writing COUNTIFS 40+ times

My formula is as below, please note that only the last criteria has to be changed that is Dash!$N$1… it will go until Column BF


=COUNTIFS(' Dump'!$D:$D,Info!$H$2,' Dump'!$G:$G,' Dash'!$H$1,' Dump'!$E:$E,' Dash'!$I$1,' Dump'!$F:$F,' Dash'!$L$1,' Dump'!$I:$I,' Dash'!$J$1,' Dump'!$B:$B,' Dash'!$K$1,' Dump'!$A:$A,' Dash'!$M$1,' Dump'!$J:$J,' Dash'!$N$1)

Thanks for help!

Hi.

=SUMPRODUCT(COUNTIFS(' Dump'!$D:$D,Info!$H$2,' Dump'!$G:$G,' Dash'!$H$1,' Dump'!$E:$E,' Dash'!$I$1,' Dump'!$F:$F,' Dash'!$L$1,' Dump'!$I:$I,' Dash'!$J$1,' Dump'!$B:$B,' Dash'!$K$1,' Dump'!$A:$A,' Dash'!$M$1,' Dump'!$J:$J,' Dash'!$N$1:$BF$1))

Regards

Originally Posted by XOR LX

Hi.

=SUMPRODUCT(COUNTIFS(' Dump'!$D:$D,Info!$H$2,' Dump'!$G:$G,' Dash'!$H$1,' Dump'!$E:$E,' Dash'!$I$1,' Dump'!$F:$F,' Dash'!$L$1,' Dump'!$I:$I,' Dash'!$J$1,' Dump'!$B:$B,' Dash'!$K$1,' Dump'!$A:$A,' Dash'!$M$1,' Dump'!$J:$J,' Dash'!$N$1:$BF$1))

Regards

This worked perfectly thanks....

For the same query as above, just for my learning, if i had a few random columns like N1, Z1, AA1, BF1 or if i had a few random rows like AE5, AE15, AE17 how to edit this formula.

You mean that all those non-contiguous cells were to be considered as the criteria for Dump'!$J:$J?

Regards

Originally Posted by XOR LX

You mean that all those non-contiguous cells were to be considered as the criteria for Dump'!$J:$J?

Regards

yes, that's correct....

If the number of those non-contiguous cells is not too great, you can use:

=SUMPRODUCT(COUNTIFS(' Dump'!$D:$D,Info!$H$2,' Dump'!$G:$G,' Dash'!$H$1,' Dump'!$E:$E,' Dash'!$I$1,' Dump'!$F:$F,' Dash'!$L$1,' Dump'!$I:$I,' Dash'!$J$1,' Dump'!$B:$B,' Dash'!$K$1,' Dump'!$A:$A,' Dash'!$M$1,' Dump'!$J:$J,CHOOSE({1,2,3,4,5,6,7},N1,Z1,AA1,BF1,AE5,AE15,AE17)))

If you need a set-up which will work for a much larger number of criteria for Dump'!$J:$J in non-contiguous cells, let me know and I will see what I can do.

Regards

Originally Posted by XOR LX

If the number of those non-contiguous cells is not too great, you can use:

=SUMPRODUCT(COUNTIFS(' Dump'!$D:$D,Info!$H$2,' Dump'!$G:$G,' Dash'!$H$1,' Dump'!$E:$E,' Dash'!$I$1,' Dump'!$F:$F,' Dash'!$L$1,' Dump'!$I:$I,' Dash'!$J$1,' Dump'!$B:$B,' Dash'!$K$1,' Dump'!$A:$A,' Dash'!$M$1,' Dump'!$J:$J,CHOOSE({1,2,3,4,5,6,7},N1,Z1,AA1,BF1,AE5,AE15,AE17)))

If you need a set-up which will work for a much larger number of criteria for Dump'!$J:$J in non-contiguous cells, let me know and I will see what I can do.

Regards

even this worked perfectly, thanks for your prompt responses.

I have a list of ~8-10 such non-contiguous cells, using the above formula is no mess at all for me.

Great! Glad to help.

Cheers