sum with multiple criteria
 

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)

sum with multiple criteria
 

=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"


--


Regards,


Peo Sjoblom




"chelle" wrote in message
...
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)

sum with multiple criteria
 

=SUMPRODUCT((A1:A4=2800650)*(C1:C4=30200028)*(B1:B 4))
--
David Biddulph

"chelle" wrote in message
...
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)

sum with multiple criteria
 

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.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=SUMPRODUCT((A1:A4=2800650)*(C1:C4=30200028)*(B1:B 4))

"chelle" wrote in message
...
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)

sum with multiple criteria
 

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.
--
Chelle


"David Biddulph" wrote:

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.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=SUMPRODUCT((A1:A4=2800650)*(C1:C4=30200028)*(B1:B 4))

"chelle" wrote in message
...
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
sum b1:b4
(in this case would = 3)

sum with multiple criteria
 

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


"chelle" wrote:

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.
--
Chelle


"David Biddulph" wrote:

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.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=SUMPRODUCT((A1:A4=2800650)*(C1:C4=30200028)*(B1:B 4))

"chelle" wrote in message
...
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
sum b1:b4
(in this case would = 3)

sum with multiple criteria
 

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


--


Regards,


Peo Sjoblom



"chelle" wrote in message
...
Why am I still getting 0? And not the value of 3?
--
Chelle


"chelle" wrote:

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.
--
Chelle


"David Biddulph" wrote:

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.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=SUMPRODUCT((A1:A4=2800650)*(C1:C4=30200028)*(B1:B 4))

"chelle" wrote in message
...
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
sum b1:b4
(in this case would = 3)

sum with multiple criteria
 

Yes I need to sum the values in column B
--
Chelle


"Peo Sjoblom" wrote:

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


--


Regards,


Peo Sjoblom

sum with multiple criteria
 

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


--


Regards,


Peo Sjoblom



"chelle" wrote in message
...
Yes I need to sum the values in column B
--
Chelle


"Peo Sjoblom" wrote:

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


--


Regards,


Peo Sjoblom