Count/Sum rows of a column depending on criteria from drop-down li

[YY san.]

Hi,
I have a question here, appreciate any help.

Prod ID Jan Feb Mar....
Prod A 10 5 3
Prod B 3 - -
Prod A 4 7 1
Prod C 5 2 7

In Cell A8 = Prod A

At cell B7, it is a data validation(drop-down list) for Jan~Dec
In B8 cell, I need a formula to calculate When Prod ID = Prod A and when
month selected is Mar, the sum, ie. result is 4.
In C8 cell, I need a formula to calculate When Prod ID = Prod A and when
month selected is Mar, the count if value is > 0, ie. result is 2

I know I may have to use sumproduct, some lookup in order to get the result.
But just couldnt get them into the correct manner.
Thanks in advance for your help.
Merry Xmas!
regards,

 

[Per Jessen]

Hi

Name the range (B2:B5) with Jan values as Jan, C2:C5 as Feb, using Insert >
Name > Define

Then in B8 calculate sum with this:

=SUMPRODUCT(--(A2:A5=A8),INDIRECT(B7))

and count in C8 with this:

=SUMPRODUCT(--(A2:A5=A8),--(INDIRECT(B7)>0))

Happy Hollidays,

Regards,
Per

 

[Jacob Skaria]

Try

=SUMPRODUCT((A1:A5=A8)*(A1:M1=B7),A1:M5)

OR

=SUMPRODUCT((A1:A5="Prod A")*(A1:M1="Mar"),A1:M5)

 

[Ms-Exl-Learner]

I assume that your data “Prod ID†starts from A1 and the value “7†(Mar) ends
in D5. So your data rang is A15.

Paste this formula in B8 Cell
=SUMPRODUCT((A2:A5=A8)*(B11=B7),(B25))

Paste this formula in C8 Cell
=SUMPRODUCT((A8=A2:A5)*(B7=B11)*(B25>0))

Change the cell reference to your desired cells, if required.

Remember to Click Yes, if this post helps!

 

[YY san.]

Thanks everyone. It works!
Have a great year ahead!!