multiple criterias in SUMIF formula

hi all,

is there any way for a sumif formula to have multiple criterias? for my case, after the formula checks for a condition, it has to check for another condition before summing up the figures.

this is my current formula:

Please Login or Register  to view this content.

E2 contains a certain id and the formula will total up the figures in column F for that particular id. i want to add another condition that the formula has to check for another condition before summing up. it has to check the description in column C for these words 'OR'. only after that it can total up for the id AND with the words 'OR'.

i want to be able to use wildcards for 'OR' since the descriptions might contain 'AW OR', 'OR (ST)' etc.

how do i add the other condition into the formula? pls help.

SUMIF can only have one condition... pre XL2007 you're looking at SUMPRODUCT to do multiple tests (in XL2007 SUMIFS was introduced) ...

=SUMPRODUCT(--($A$1:$A$1000=$E2),--ISNUMBER(SEARCH(" OR "," "&$C$1:$C$1000&" ")),$F$1:$F$1000)

NOTE: you can not use entire column references in Sumproduct (pre XL2007) and you should ensure you keep ranges to a minimum to avoid performance issues.

On an aside you can also use Concatentation to create one unique identifier thereby negating need for Sumproduct / Array, eg:

Z1: =$A1&":"&ISNUMBER(SEARCH(" OR "," "&$C1&" "))
copied down for all rows

Then SUMPRODUCT becomes a more efficient SUMIF

=SUMIF(Z:Z,$E2&":TRUE",F:F)

hi all. unfortunately mine is a very big file with thousands of data.

i have attached a sample workbook.

in Worksheet, E21 takes in the sum of the VOLUME found in REP09 based on the id from E2.
i am now working on D9:D12 where i have to take in the total numbers from PERCENTAGES found in REP09 based on the id from E2 and the descriptions in
B9:B12.

they are supposed to total up for each desc, say for ON US, the formula should sum up VC ON US and MC ON US in REP09 based on the id.

thanks in adv.

Based purely on your sample file:

Please Login or Register  to view this content.

thanks DonkeyOte. the formula works just fine.

so may I ask why you're not using it in your latest file ?
the formulae you're using in E9:E11 in your latest example are arguably less efficient, moreover you're not using absolute references which may lead to issues going forward, I would strongly advise implementing the formula previously provided.

problem is now, i have another column for a new condition. after getting the percentages based on the id and description, i have to look at a new column, column G and find out if the description match the codes 99.
if the descriptions "MC ON US" and "VC ON US" match the codes 99 in column G, instead of dumping into cells E9, each have to be displayed in cells E13 and E14 according to the descriptions.

based on the original formula provided, revised with additional test highlighted in red for your convenience:

Please Login or Register  to view this content.