| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to retrieve multiple values in multiple rows with one criteria | Excel Discussion (Misc queries) | |||
| Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
| return multiple records matching multiple criteria | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
