To sumifs or sumproduct?

M

mlomagno

Joined
Oct 30, 2012
Messages
2
Reaction score
0
Hi all,

Hope someone can help. I've been trying to figure this out on my own but have failed. Any help will be appreciated.

Here is the issue, have a formula that works fine when summing up one column. However, I can't do it when I want to add additional columns to be included in the calculation. Here is an example of the formula used: =SUM(SUMIFS(D:D,A:A,1,B:B,"A560",C:C,"*BA*",H:H,2012,I:I,9)). It is summing up column D based on criteria from other columns. The result is 3500. However, I would like to change the formula to sum not only column D but also E, F, G and keep criteria the same. The result would then include the last row of data and the new sum would be 2000. How do I sum up columns D thru F based on several criteria. Thanks in advance to all. -Michael
 

Attachments

3 things:
1. what version of Excel are you using? Different formulas are available depending on the version. Specifically, SUMIFS, COUNTIFS, and AVERAGEIFS.
2. Why are you using a SUM outside of a SUMIFS? The SUMIFS formula will already sum up the range you specify, so the SUM shouldn't be necessary.
3. Instead of using A:A and B:B for your ranges, you should really specify the actual range. I'm assuming you're using at least Excel 2007, which would make this formula available to you. In that case, using A:A and B:B for your ranges is trying to calculate over 1,000,000 rows for each part and could cause problems and slow things down in the long run.
 
1. Using excel 2010
2. using the sum function outside of the sumif because it was not pulling all the criteria. why don't you try it and see what happens? What I put in the table is a sample. However, in actual data there are several prefixes that have to be accounted for. When I did it as a sumifs formula, it was not pulling up the exact prefixes. it was not resolved until putting the sum in front of formula.
3. even with putting a finite range, I am still coming up with an error message.
 
Back
Top