countif quary

  • Thread starter Thread starter Jai
  • Start date Start date
J

Jai

Hi,
I have one quary in excel which I would like to ask while sending
excel file but how can I attach my excel file to you?
Basically my quary is; I want to count the volume of different
products ( in somany colmns like b,c,d,e,f etc) towards a specific
name appears in colomn a in many rows and the count should be greater
then 0 before each colomn in b,c,d,e,f etc while ignoring minus
volume.

Regards.
Jaipal
 
Let's say the first three rows have this data (I will only go to column D)
cat 2 3 5
dog 1 5 3
cat 4 6 -2

You want an answer like
Cat = 20
Dog = 9

This formula will give 20: =SUMPRODUCT((A1:A3="cat")*(B1:D3>0)*B1:D3)
If G1 has the value 'cat', then this will give 20:
=SUMPRODUCT(($A$1:$A$3=G1)*($B$1:$D$3>0)*$B$1:$D$3)
And if G2 had 'dog', then you can use
=SUMPRODUCT(($A$1:$A$3=G2)*($B$1:$D$3>0)*$B$1:$D$3)

You can make the formula work with more rows using something like
=SUMPRODUCT(($A$1:$A$100=G1)*($B$1:$D$100>0)*$B$1:$D$100)

Hope this helps
best wishes
 
Back
Top