# Thread: Solved: Sumproduct short question - count in sumproduct

1. ## Solved: Sumproduct short question - count in sumproduct

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

2. 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)

3. 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).

4. 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

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•