sumproduct function returns #value or #ref error

Hi Jennie

Take a look at the COUNTIF() formula.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Jennie" <Jennie@discussions.microsoft.com> skrev i en meddelelse
news:9C4A024C-7B22-4075-8ACF-13C401BA2F91@microsoft.com...
> 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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
> news:9C4A024C-7B22-4075-8ACF-13C401BA2F91@microsoft.com...
> > 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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
news:4E91973A-147D-49AB-94B0-035BC6790664@microsoft.com...
>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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
>> news:9C4A024C-7B22-4075-8ACF-13C401BA2F91@microsoft.com...
>> > 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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
news:9C4A024C-7B22-4075-8ACF-13C401BA2F91@microsoft.com...
> 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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
> news:9C4A024C-7B22-4075-8ACF-13C401BA2F91@microsoft.com...
> > 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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
news:4E91973A-147D-49AB-94B0-035BC6790664@microsoft.com...
>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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
>> news:9C4A024C-7B22-4075-8ACF-13C401BA2F91@microsoft.com...
>> > 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 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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
news:9C4A024C-7B22-4075-8ACF-13C401BA2F91@microsoft.com...
> 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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
> news:9C4A024C-7B22-4075-8ACF-13C401BA2F91@microsoft.com...
> > 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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
news:4E91973A-147D-49AB-94B0-035BC6790664@microsoft.com...
>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" <Jennie@discussions.microsoft.com> skrev i en meddelelse
>> news:9C4A024C-7B22-4075-8ACF-13C401BA2F91@microsoft.com...
>> > 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.
>>
>>
>>