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

Y

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,
 
P

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
 
J

Jacob Skaria

Try

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

OR

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

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 A1:D5.

Paste this formula in B8 Cell
=SUMPRODUCT((A2:A5=A8)*(B1:D1=B7),(B2:D5))

Paste this formula in C8 Cell
=SUMPRODUCT((A8=A2:A5)*(B7=B1:D1)*(B2:D5>0))

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

Remember to Click Yes, if this post helps!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top