sum with multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chelle - 24 Sep 2007 21:16 GMT

I'm trying to add the values in a column if the row includes two different
criteria:

2800650    1 30200028
2800650    1 30200028
2800650    1 30200028
2800750    1 30200028

if the value in column a="2800650", and the value in colum c="30200028" then
count b1:b4
(in this case would = 3)

Reply to this Message

Peo Sjoblom - 24 Sep 2007 21:25 GMT

=SUMPRODUCT--(B1:B100=2800650),--(C1:C100=30200028))

replace the hard coded values with cells like

=SUMPRODUCT--(B1:B100=D2),--(C1:C100=E2))

where you put the criteria in the cells, just a heads up, if the values are
text numbers the formula will return zero, if so try to enclose the criteria
in quotes like

"2800650"

Signature

Regards,

Peo Sjoblom

> I'm trying to add the values in a column if the row includes two different
> criteria:
[quoted text clipped - 8 lines]
> count b1:b4
> (in this case would = 3)

Reply to this Message

David Biddulph - 24 Sep 2007 21:30 GMT

=SUMPRODUCT((A1:A4=2800650)*(C1:C4=30200028)*(B1:B4))

Signature

David Biddulph

> I'm trying to add the values in a column if the row includes two different
> criteria:
[quoted text clipped - 8 lines]
> count b1:b4
> (in this case would = 3)

Reply to this Message

David Biddulph - 24 Sep 2007 21:35 GMT

Looking again at your original question, I see that I was reading it as sum
b1:b4, given the criteria in columns a and c, but that you actually said
"count b1:b4". Do the values in b1:b4 matter, or are you merely counting
the rows in which the a and c criteria are met? If you're only counting
rows and the column b values are irrelevant, omit the *(B1:B4) term in my
equation.

Signature

David Biddulph

> =SUMPRODUCT((A1:A4=2800650)*(C1:C4=30200028)*(B1:B4))

>> I'm trying to add the values in a column if the row includes two
>> different
[quoted text clipped - 9 lines]
>> count b1:b4
>> (in this case would = 3)

Reply to this Message

chelle - 24 Sep 2007 22:02 GMT

The value in column b does matter, so I should have said sum. Occasionally
the value is -1, which is where I started having problems.

Signature

Chelle

> Looking again at your original question, I see that I was reading it as sum
> b1:b4, given the criteria in columns a and c, but that you actually said
[quoted text clipped - 17 lines]
> >> sum b1:b4
> >> (in this case would = 3)

Reply to this Message

chelle - 24 Sep 2007 22:12 GMT

Why am I still getting 0? And not the value of 3?

Signature

Chelle

> The value in column b does matter, so I should have said sum. Occasionally
> the value is -1, which is where I started having problems.
[quoted text clipped - 20 lines]
> > >> sum b1:b4
> > >> (in this case would = 3)

Reply to this Message

Peo Sjoblom - 24 Sep 2007 22:21 GMT

Text maybe, what happens if you test the values with =ISNUMBER(A1)
=ISNUMBER(B1)
then copy down as long as needed to cover the values in A and B
if you get any false then you have text and I showed how you could correct
that unless you want to sum the values as opposed to count them

Signature

Regards,

Peo Sjoblom

> Why am I still getting 0? And not the value of 3?
>
[quoted text clipped - 29 lines]
>> > >> sum b1:b4
>> > >> (in this case would = 3)

Reply to this Message

chelle - 24 Sep 2007 22:58 GMT

Yes I need to sum the values in column B

Signature

Chelle

> Text maybe, what happens if you test the values with =ISNUMBER(A1)
> =ISNUMBER(B1)
> then copy down as long as needed to cover the values in A and B
> if you get any false then you have text and I showed how you could correct
> that unless you want to sum the values as opposed to count them

Reply to this Message

Peo Sjoblom - 24 Sep 2007 23:07 GMT

So did you test if they were text using those formulas (you can also use
=ISTEXT(A1))

Signature

Regards,

Peo Sjoblom

> Yes I need to sum the values in column B
>
[quoted text clipped - 4 lines]
>> correct
>> that unless you want to sum the values as opposed to count them

Reply to this Message