Add if multiple criteria met including some criteria that share a column

[HelenClark]

Hi
I have all data on one spreadsheet and need it to calculate on another worksheet if multiple criteria is met, the problem im having is asking Excel to calculate if there is more than one criteria in the same column.

Im using the following formula but it will only calculate the cells in column C with (3) Tender, i need it to also count if it has (4) Presentation

=SUMIFS(Breakdown!F2:F10,Breakdown!B2:B10,"WS",Breakdown!C2:C10,{"(3) Tender","(4) Presentation"},Breakdown!H2:H10,"New Business")

I have attached the spreadsheet, basically im trying to ask Excel to add up the values in column F if column B has WS and column C has (3) Tender or (4) Presentation and column H has the words New Business

side note - i have to have (3) and (4) with brackets as this is how it exports from our database - can delete each time if i really have to though

[XOR LX]

Hi,

Just wrap your existing attempt in SUM, i.e.:

=SUM(SUMIFS(Breakdown!F2:F10,Breakdown!B2:B10,"WS",Breakdown!C2:C10,{"(3) Tender","(4) Presentation"},Breakdown!H2:H10,"New Business"))

Regards

[JohnTopley]

Try

in F4

=SUMPRODUCT(SUMIFS(Breakdown!F2:F10,Breakdown!B2:B10,"WS",Breakdown!C2:C10,{"(3) Tender","(4) Presentation"},Breakdown!H2:H10,"New Business"))

[HelenClark]

Sorted - thank you very much