|
Nesting SUMIF formulas
Can I nest a SUMIF within a SUMIF formula?
|
Nesting SUMIF formulas
Yes. Upto 7 i believe but how are you planning on doing it? -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758 View this thread: http://www.excelforum.com/showthread...hreadid=484302 |
Nesting SUMIF formulas
No you can't. Post the problem and you'll probably will receive a solution
though -- Regards, Peo Sjoblom "Todd" wrote in message ... Can I nest a SUMIF within a SUMIF formula? |
Nesting SUMIF formulas
I have a file set up in database fashion that I am trying to find the easiest
way to summarize data using more than one criteria. I am also looking into using tables. This database gets added to and resorted frequently so I did not want to use Subtotals. For instance a database of automobiles that includes Make, Model and several other distinguishing fields. I was trying to use a SUMIF formula to give me the total of all the Ford Explorers vs. Jeep Cherokees vs. Dodge Rams etc. I could not figure out how to actually write a nested SUMIF formula on both the Make and Model fields. I am also looking into using tables. Todd "chalky" wrote: Yes. Upto 7 i believe but how are you planning on doing it? -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758 View this thread: http://www.excelforum.com/showthread...hreadid=484302 |
Nesting SUMIF formulas
Use
=SUMPRODUCT(--(Make_Range="Ford"),--(Model_Range="Explorer"),Sum_Range) replace the hardcoded Ford and Explorer with cells like B2 and C2 in where you would type the make and model thus avoiding to having to edit the formula when you change make/model Ranges need to be of the same size and cannot be the whole column like A:A as opposed to A2:A100 -- Regards, Peo Sjoblom "Todd" wrote in message ... I have a file set up in database fashion that I am trying to find the easiest way to summarize data using more than one criteria. I am also looking into using tables. This database gets added to and resorted frequently so I did not want to use Subtotals. For instance a database of automobiles that includes Make, Model and several other distinguishing fields. I was trying to use a SUMIF formula to give me the total of all the Ford Explorers vs. Jeep Cherokees vs. Dodge Rams etc. I could not figure out how to actually write a nested SUMIF formula on both the Make and Model fields. I am also looking into using tables. Todd "chalky" wrote: Yes. Upto 7 i believe but how are you planning on doing it? -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758 View this thread: http://www.excelforum.com/showthread...hreadid=484302 |
Nesting SUMIF formulas
The formula you have posted resolves a problem I have been having.
However, I cannot see why it works (as SUMPRODUCT multipllies corresponding components in the given arrays and sums the answer). How can arrays that don't have values be multiplied. Are the minus signs significant here. I notice that if you remove them the formula doesnt work "Peo Sjoblom" wrote: Use =SUMPRODUCT(--(Make_Range="Ford"),--(Model_Range="Explorer"),Sum_Range) replace the hardcoded Ford and Explorer with cells like B2 and C2 in where you would type the make and model thus avoiding to having to edit the formula when you change make/model Ranges need to be of the same size and cannot be the whole column like A:A as opposed to A2:A100 -- Regards, Peo Sjoblom "Todd" wrote in message ... I have a file set up in database fashion that I am trying to find the easiest way to summarize data using more than one criteria. I am also looking into using tables. This database gets added to and resorted frequently so I did not want to use Subtotals. For instance a database of automobiles that includes Make, Model and several other distinguishing fields. I was trying to use a SUMIF formula to give me the total of all the Ford Explorers vs. Jeep Cherokees vs. Dodge Rams etc. I could not figure out how to actually write a nested SUMIF formula on both the Make and Model fields. I am also looking into using tables. Todd "chalky" wrote: Yes. Upto 7 i believe but how are you planning on doing it? -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758 View this thread: http://www.excelforum.com/showthread...hreadid=484302 |
Nesting SUMIF formulas
Hi
The tests will return TRUE or FALSE. Multiplying by the double unary (--) coerces these results to 1 and 0 respectively which are then used within the Sumproduct array calculation. -- Regards Roger Govier "Phil" wrote in message ... The formula you have posted resolves a problem I have been having. However, I cannot see why it works (as SUMPRODUCT multipllies corresponding components in the given arrays and sums the answer). How can arrays that don't have values be multiplied. Are the minus signs significant here. I notice that if you remove them the formula doesnt work "Peo Sjoblom" wrote: Use =SUMPRODUCT(--(Make_Range="Ford"),--(Model_Range="Explorer"),Sum_Range) replace the hardcoded Ford and Explorer with cells like B2 and C2 in where you would type the make and model thus avoiding to having to edit the formula when you change make/model Ranges need to be of the same size and cannot be the whole column like A:A as opposed to A2:A100 -- Regards, Peo Sjoblom "Todd" wrote in message ... I have a file set up in database fashion that I am trying to find the easiest way to summarize data using more than one criteria. I am also looking into using tables. This database gets added to and resorted frequently so I did not want to use Subtotals. For instance a database of automobiles that includes Make, Model and several other distinguishing fields. I was trying to use a SUMIF formula to give me the total of all the Ford Explorers vs. Jeep Cherokees vs. Dodge Rams etc. I could not figure out how to actually write a nested SUMIF formula on both the Make and Model fields. I am also looking into using tables. Todd "chalky" wrote: Yes. Upto 7 i believe but how are you planning on doing it? -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758 View this thread: http://www.excelforum.com/showthread...hreadid=484302 |
| All times are GMT +1. The time now is 02:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com