Sumproduct short question - count in sumproduct

[danovkos]

Hi all,
i have one short question.
How can i use function COUNT in SUMPRODUCT function?
is it possible?
i tried something like this:

=SUMPRODUCT(--(Prer!$D$4:$D$2000=Prer!$D$3);--(Prer!$M$4:$M$2000<>"C");--(Prer!$R$4:$R$2000="R");COUNTA(Prer!$C$4:$C$1500))
but it doesnt works
Till now i use counting in sumproduct with column, where was only number 1 and it counted basics this column.
It seems like this:

=SUMPRODUCT(--(Prer!$D$4:$D$2000=Prer!$D$3);--(Prer!$M$4:$M$2000<>"C");--(Prer!$R$4:$R$2000="R");Prer!$Z$4:$Z$1500)

where column Z was only for help with number 1.

But this is not the right way in each case.

how can i fix it?
thx a lot

[mdmackillop]

COUNTA produces a single number, not an array. To multiply by this, take it out of the SUMPRODUCT


=SUMPRODUCT(--(Prer!$D$4:$D$2000=Prer!$D$3);--(Prer!$M$4:$M$2000<>"C");--(Prer!$R$4:$R$2000="R"))*COUNTA(Prer!$C$4:$C$1500)

[danovkos]

I dont know why, but this return a very big number (2800 instead 9). Not the same as return if i use my old method with help column. Something can be wrong?
I use sumpruduct and multiply it with counta (this count names in column).

[danovkos]

here is example (atach.)
in blue is your formula but with wrong result for me and cell next to (yellow) my old formula with right result.
How can i achive right result but with count and sumproduct formula? or other way, but without help column?
thx

[Bob Phillips]

=SUMPRODUCT(--($A$3:$A$1622="R"),--($C$3:$C$1622=100))

[danovkos]

oh god! :)
so simply...
yes, this works
thank you XLD and
MDM also thank you for your time