sumproduct function returns #value or #ref error
 

hi all,
I'm trying to make a summary sheet for my inventory sheet that will tell me
the total #of product (types a, b, c...) produced in a certain month. My
inventory sheet looks something like this:

Month Product
1 a
1 a
1 b
2 a
3 a
3 c

For my summary, say I want to enter the month of interest in cell c1. So, if
I'm interested in month 1, the cell in which my formula is in will spit out 3
as the total number of product produced.
I tried both =sumproduct((month(b2:b6)=c1)*1) and
=sumproduct((a2:a6(b2:b6)=c1)*1) which give me #value and #ref errors,
respectively. What am I doing wrong? Is there another formula I could try?

Thanks in advance.

Hi Jennie

Take a look at the COUNTIF() formula.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Jennie" skrev i en meddelelse
...
hi all,
I'm trying to make a summary sheet for my inventory sheet that will tell
me
the total #of product (types a, b, c...) produced in a certain month. My
inventory sheet looks something like this:

Month Product
1 a
1 a
1 b
2 a
3 a
3 c

For my summary, say I want to enter the month of interest in cell c1. So,
if
I'm interested in month 1, the cell in which my formula is in will spit
out 3
as the total number of product produced.
I tried both =sumproduct((month(b2:b6)=c1)*1) and
=sumproduct((a2:a6(b2:b6)=c1)*1) which give me #value and #ref errors,
respectively. What am I doing wrong? Is there another formula I could try?

Thanks in advance.

sorry leo, your post is not showing up

"Jennie" wrote:

hi all,
I'm trying to make a summary sheet for my inventory sheet that will tell me
the total #of product (types a, b, c...) produced in a certain month. My
inventory sheet looks something like this:

Month Product
1 a
1 a
1 b
2 a
3 a
3 c

For my summary, say I want to enter the month of interest in cell c1. So, if
I'm interested in month 1, the cell in which my formula is in will spit out 3
as the total number of product produced.
I tried both =sumproduct((month(b2:b6)=c1)*1) and
=sumproduct((a2:a6(b2:b6)=c1)*1) which give me #value and #ref errors,
respectively. What am I doing wrong? Is there another formula I could try?

Thanks in advance.

I don't know how to make the countif formula work if I have 3 parameters. It
only works with 2. I already tried something like countif(a2:a6=c1,b2:b6) and
the program doesn't understand this command.

Any other suggestions, please?????

"Leo Heuser" wrote:

Hi Jennie

Take a look at the COUNTIF() formula.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Jennie" skrev i en meddelelse
...
hi all,
I'm trying to make a summary sheet for my inventory sheet that will tell
me
the total #of product (types a, b, c...) produced in a certain month. My
inventory sheet looks something like this:

Month Product
1 a
1 a
1 b
2 a
3 a
3 c

For my summary, say I want to enter the month of interest in cell c1. So,
if
I'm interested in month 1, the cell in which my formula is in will spit
out 3
as the total number of product produced.
I tried both =sumproduct((month(b2:b6)=c1)*1) and
=sumproduct((a2:a6(b2:b6)=c1)*1) which give me #value and #ref errors,
respectively. What am I doing wrong? Is there another formula I could try?

Thanks in advance.

What exactly are you trying to count? Your first
posting, with a solution of 3, apparently just counted
the number of ones in column A.
Do you want the number of a's(2), b's(1) and c's(0) for the month in C1

Is this the formula, you are looking for:

=SUMPRODUCT((A1:A100=C1)*(B1:B100={"a","b","c"}))

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Jennie" skrev i en meddelelse
...
I don't know how to make the countif formula work if I have 3 parameters.
It
only works with 2. I already tried something like countif(a2:a6=c1,b2:b6)
and
the program doesn't understand this command.

Any other suggestions, please?????

"Leo Heuser" wrote:

Hi Jennie

Take a look at the COUNTIF() formula.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Jennie" skrev i en meddelelse
...
hi all,
I'm trying to make a summary sheet for my inventory sheet that will
tell
me
the total #of product (types a, b, c...) produced in a certain month.
My
inventory sheet looks something like this:

Month Product
1 a
1 a
1 b
2 a
3 a
3 c

For my summary, say I want to enter the month of interest in cell c1.
So,
if
I'm interested in month 1, the cell in which my formula is in will spit
out 3
as the total number of product produced.
I tried both =sumproduct((month(b2:b6)=c1)*1) and
=sumproduct((a2:a6(b2:b6)=c1)*1) which give me #value and #ref errors,
respectively. What am I doing wrong? Is there another formula I could
try?

Thanks in advance.