sum with multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
=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
 
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.
 
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.
 
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 said:
Why am I still getting 0? And not the value of 3?
--
Chelle


chelle said:
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 said:
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:B4))

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)
Click to expand...
Click to expand...
Click to expand...
 
So did you test if they were text using those formulas (you can also use
=ISTEXT(A1))


--


Regards,


Peo Sjoblom
 
Back
Top