You are here:

# Excel/sumifs

Question
Good day Bob

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
criteria2], …)

=SUMIFS(A2:A9, C2:C9, "=A*", D2:D9, 1)

In the sum_range, instead of summing one column (ex: A2:A9), I would like to sum the multiplication of 2 columns.

To make myself clearer, lets take this example:
Let's say that only row 4 & 7 meet ALL my criterias
I therefore would like the SUMIFS (sum_range) to do this:
(A4*B4) + (A7*B7)

Tried but failed.

I understand that I can create a column having A*B and do the sum_range on this column but this creates another column and I would like to evaluate other possibilities...

Do you have an idea?

Thank you

Hi Daniel,

This is a perfect situation for using SUMPRODUCT which allows for what you are trying to do (plus many other tweaks that SUMIF/SUMIFS just can't handle).

The syntax would be

=SUMPRODUCT((LEFT(C2:C9,1)="A")*(D2:D9=1)*(A2:A9*B2:B9))

The expressions in the first two sets of brackets evaluate to an array of TRUE/FALSE values, which when multiplied together give an array of 1/0 (one means bothe conditions are met, o means one or both is not met). Each element of this composite array is then multiplied by each element of the numbers multiplied array, A2:A9*B2:B9, the 1 create an array element of An*bn, the 0 creates an array element of 0, so adding these up we get the correct result.
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Well done!

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Bob Phillips

##### Expertise

Can - VBA, Functions and formulas, PowerPivot, conditional formatting, data validation, charting

##### Experience

I have been using Excel for in excess of 20 yeras, and I am a 9 year Microsoft MVP

Organizations
PASS UK Developer Group SQL Soton SQL FAQ

Education/Credentials
BSc Mathematics

Awards and Honors
Microsoft MVP since 2005