PDA

View Full Version : Solved: Sumproduct short question - count in sumproduct

danovkos
09-04-2009, 01:44 AM
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
09-04-2009, 02:44 AM
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
09-04-2009, 03:03 AM
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
09-04-2009, 04:15 AM
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
09-04-2009, 04:34 AM
=SUMPRODUCT(--(\$A\$3:\$A\$1622="R"),--(\$C\$3:\$C\$1622=100))

danovkos
09-04-2009, 04:38 AM
oh god! :)
so simply...
yes, this works
thank you XLD and
MDM also thank you for your time