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,

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,

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,


.

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,

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,