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

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,

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

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


"YY san." skrev i meddelelsen
...
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,

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

Try

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

OR

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

--
Jacob


"YY san." wrote:

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,

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

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:D50))

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"YY san." wrote:

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,

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

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

"Per Jessen" wrote:

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


"YY san." skrev i meddelelsen
...
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,


.