| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with 2 criteria
I have tried the SUMProduct but have been unable to get it to work. I want to
add column J if A = 2003 and IVZ and so on for each year. =SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30) What am i doing wrong? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with 2 criteria
"ruby" wrote: I have tried the SUMProduct but have been unable to get it to work. I want to add column J if A = 2003 and c=IVZ and so on for each year. =SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30) What am i doing wrong? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with 2 criteria
If Column A has numbers in it, then you should test them against numbers
(2003), not a text string ("2003"). Try this... =SUMPRODUCT(--($A$8:$A$30=2003),--($C$8:$C$30="IVZ"),$J$8:$J$30) Now, if Column A contains Dates and not numbers (your mention of the word 'year' makes me wonder what is actually in Column A's cells), then maybe this will work... =SUMPRODUCT(--(Year($A$8:$A$30)=2003),--($C$8:$C$30="IVZ"),$J$8:$J$30) -- Rick (MVP - Excel) "ruby" wrote in message ... I have tried the SUMProduct but have been unable to get it to work. I want to add column J if A = 2003 and IVZ and so on for each year. =SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30) What am i doing wrong? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with 2 criteria
Try changing "2003" to 2003
-- Don Guillett Microsoft MVP Excel SalesAid Software "ruby" wrote in message ... "ruby" wrote: I have tried the SUMProduct but have been unable to get it to work. I want to add column J if A = 2003 and c=IVZ and so on for each year. =SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30) What am i doing wrong? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with 2 criteria
Hi,
And if you really want to be cute: =SUMPRODUCT(--(A8:A30&C8:C30=2003&"IVZ"),D8:D30) -- If this helps, please click the Yes button Cheers, Shane Devenshire "ruby" wrote: I have tried the SUMProduct but have been unable to get it to work. I want to add column J if A = 2003 and IVZ and so on for each year. =SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30) What am i doing wrong? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with 2 criteria
Two questions... Why didn't you write it as "2003IVZ" instead of
concatenating two constants? What will your formula do if, for a particular row, either Column A's cell = "2003IVZ and Column C's cell is blank or vice versa? -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, And if you really want to be cute: =SUMPRODUCT(--(A8:A30&C8:C30=2003&"IVZ"),D8:D30) -- If this helps, please click the Yes button Cheers, Shane Devenshire "ruby" wrote: I have tried the SUMProduct but have been unable to get it to work. I want to add column J if A = 2003 and IVZ and so on for each year. =SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30) What am i doing wrong? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with 2 criteria
Hi Rick
Tried you way but it didnt work! This is an example of the spreadsheet. Date of Receipt Tax year Code Stock Amount Received 04/05/2004 2004 IVZ INVESCO 15.27 15/10/2004 2004 IVZ INVESCO 16.50 04/05/2005 2005 IVZ INVESCO 33.00 Code Security Total 2004 2005 IVZ Invesco In Column K2 (2004) I want the total IVZ (B2)for 2004 (C2) =SUMPRODUCT(--(B:B&C:C=2003&"IVZ"),E:E) - got #NUM! =SUMPRODUCT(--(Purchase!$B$1:$B$30=$K$1),--(Purchase!$C$1:$C$30=H2),Purchase!$E$1:$E$30) Neither worked? "Rick Rothstein" wrote: Two questions... Why didn't you write it as "2003IVZ" instead of concatenating two constants? What will your formula do if, for a particular row, either Column A's cell = "2003IVZ and Column C's cell is blank or vice versa? -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, And if you really want to be cute: =SUMPRODUCT(--(A8:A30&C8:C30=2003&"IVZ"),D8:D30) -- If this helps, please click the Yes button Cheers, Shane Devenshire "ruby" wrote: I have tried the SUMProduct but have been unable to get it to work. I want to add column J if A = 2003 and IVZ and so on for each year. =SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30) What am i doing wrong? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with 2 criteria
=SUMPRODUCT(--(Purchase!$B$1:$B$30=$K$1),--(Purchase!$C$1:$C$30=H2),Purchase!$E$1:$E$30)
Using the above formula, shouldn't the $K$1 reference be $K$2... your data starts on the 2nd row, correct? Also, if you are going to copy this formula down, you need to remove the $ signs (absolute reference) from $K$2 and make it K2 (that would make it like the H2 you have in the other part of the expression). I would also change the references to Row 1 in the range to Row 2. Give this formula a try and see if it works for you... =SUMPRODUCT(--(Purchase!$B$1:$B$30=K2),--(Purchase!$C$2:$C$30=H2),Purchase!$E$2:$E$30)--Rick (MVP - Excel)"ruby" wrote in ... Hi Rick Tried you way but it didnt work! This is an example of the spreadsheet. Date of Receipt Tax year Code Stock Amount Received 04/05/2004 2004 IVZ INVESCO 15.27 15/10/2004 2004 IVZ INVESCO 16.50 04/05/2005 2005 IVZ INVESCO 33.00 Code Security Total 2004 2005 IVZ Invesco In Column K2 (2004) I want the total IVZ (B2)for 2004 (C2) =SUMPRODUCT(--(B:B&C:C=2003&"IVZ"),E:E) - got #NUM!=SUMPRODUCT(--(Purchase!$B$1:$B$30=$K$1),--(Purchase!$C$1:$C$30=H2),Purchase!$E$1:$E$30) Neither worked? "Rick Rothstein" wrote: Two questions... Why didn't you write it as "2003IVZ" instead of concatenating two constants? What will your formula do if, for aparticular row, either Column A's cell = "2003IVZ and Column C's cell is blank orvice versa? -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, And if you really want to be cute: =SUMPRODUCT(--(A8:A30&C8:C30=2003&"IVZ"),D8:D30) -- If this helps, please click the Yes button Cheers, Shane Devenshire "ruby" wrote: I have tried the SUMProduct but have been unable to get it to work. I want to add column J if A = 2003 and IVZ and so on for each year. =SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30) What am i doing wrong? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with 2 criteria
Hi
=SUMPRODUCT(--($B$1:$B$300=$P$1),--($C$1:$C$300=$H$2),$E$1:$E$300) Worked perfectly, but i have another question, why do i need to specify a range as in b1 to b300, why if i change to B:B and so on does this fail? PS. Thanks for your help, your a legend! "Rick Rothstein" wrote: =SUMPRODUCT(--(Purchase!$B$1:$B$30=$K$1),--(Purchase!$C$1:$C$30=H2),Purchase!$E$1:$E$30) Using the above formula, shouldn't the $K$1 reference be $K$2... your data starts on the 2nd row, correct? Also, if you are going to copy this formula down, you need to remove the $ signs (absolute reference) from $K$2 and make it K2 (that would make it like the H2 you have in the other part of the expression). I would also change the references to Row 1 in the range to Row 2. Give this formula a try and see if it works for you... =SUMPRODUCT(--(Purchase!$B$1:$B$30=K2),--(Purchase!$C$2:$C$30=H2),Purchase!$E$2:$E$30)--Rick (MVP - Excel)"ruby" wrote in ... Hi Rick Tried you way but it didnt work! This is an example of the spreadsheet. Date of Receipt Tax year Code Stock Amount Received 04/05/2004 2004 IVZ INVESCO 15.27 15/10/2004 2004 IVZ INVESCO 16.50 04/05/2005 2005 IVZ INVESCO 33.00 Code Security Total 2004 2005 IVZ Invesco In Column K2 (2004) I want the total IVZ (B2)for 2004 (C2) =SUMPRODUCT(--(B:B&C:C=2003&"IVZ"),E:E) - got #NUM!=SUMPRODUCT(--(Purchase!$B$1:$B$30=$K$1),--(Purchase!$C$1:$C$30=H2),Purchase!$E$1:$E$30) Neither worked? "Rick Rothstein" wrote: Two questions... Why didn't you write it as "2003IVZ" instead of concatenating two constants? What will your formula do if, for aparticular row, either Column A's cell = "2003IVZ and Column C's cell is blank orvice versa? -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, And if you really want to be cute: =SUMPRODUCT(--(A8:A30&C8:C30=2003&"IVZ"),D8:D30) -- If this helps, please click the Yes button Cheers, Shane Devenshire "ruby" wrote: I have tried the SUMProduct but have been unable to get it to work. I want to add column J if A = 2003 and IVZ and so on for each year. =SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30) What am i doing wrong? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with 2 criteria
It's a built-in limitation (for most array-processing functions) in versions
of Excel prior to XL2007 (the restriction was lifted in XL2007). However, it is probably a good thing, otherwise the formula would end up doing calculations for every row even when there is no data to be processed in those rows... it is more efficient to limit array calculations as much as possible to the cells where there is (or could be) actual data to process. -- Rick (MVP - Excel) "ruby" wrote in message ... Hi =SUMPRODUCT(--($B$1:$B$300=$P$1),--($C$1:$C$300=$H$2),$E$1:$E$300) Worked perfectly, but i have another question, why do i need to specify a range as in b1 to b300, why if i change to B:B and so on does this fail? PS. Thanks for your help, your a legend! "Rick Rothstein" wrote: =SUMPRODUCT(--(Purchase!$B$1:$B$30=$K$1),--(Purchase!$C$1:$C$30=H2),Purchase!$E$1:$E$30) Using the above formula, shouldn't the $K$1 reference be $K$2... your data starts on the 2nd row, correct? Also, if you are going to copy this formula down, you need to remove the $ signs (absolute reference) from $K$2 and make it K2 (that would make it like the H2 you have in the other part of the expression). I would also change the references to Row 1 in the range to Row 2. Give this formula a try and see if it works for you... =SUMPRODUCT(--(Purchase!$B$1:$B$30=K2),--(Purchase!$C$2:$C$30=H2),Purchase!$E$2:$E$30)--Rick (MVP - Excel)"ruby" wrote in ... Hi Rick Tried you way but it didnt work! This is an example of the spreadsheet. Date of Receipt Tax year Code Stock Amount Received 04/05/2004 2004 IVZ INVESCO 15.27 15/10/2004 2004 IVZ INVESCO 16.50 04/05/2005 2005 IVZ INVESCO 33.00 Code Security Total 2004 2005 IVZ Invesco In Column K2 (2004) I want the total IVZ (B2)for 2004 (C2) =SUMPRODUCT(--(B:B&C:C=2003&"IVZ"),E:E) - got #NUM!=SUMPRODUCT(--(Purchase!$B$1:$B$30=$K$1),--(Purchase!$C$1:$C$30=H2),Purchase!$E$1:$E$30) Neither worked? "Rick Rothstein" wrote: Two questions... Why didn't you write it as "2003IVZ" instead of concatenating two constants? What will your formula do if, for aparticular row, either Column A's cell = "2003IVZ and Column C's cell is blank orvice versa? -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, And if you really want to be cute: =SUMPRODUCT(--(A8:A30&C8:C30=2003&"IVZ"),D8:D30) -- If this helps, please click the Yes button Cheers, Shane Devenshire "ruby" wrote: I have tried the SUMProduct but have been unable to get it to work. I want to add column J if A = 2003 and IVZ and so on for each year. =SUMPRODUCT(--($A$8:$A$30="2003"),--($C$8:$C$30="IVZ"),$J$8:$J$30) What am i doing wrong? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif with four criteria??? | Excel Discussion (Misc queries) | |||
| SUMIF with four criteria | Excel Worksheet Functions | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| Sumif() with criteria | Excel Worksheet Functions | |||
| Criteria with "<" or ">" in sumif() | Excel Discussion (Misc queries) | |||
Linear Mode
