To sumifs or sumproduct?

[mlomagno]

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,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

 

[alow]

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.

 

[mlomagno]

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.