Complex SUMIF - need to add a function to exclude a certain condition I have two

Question

Complex SUMIF - need to add a function to exclude a certain condition

I have two tabs:

SPORT SUMMARY 2015 PRIM - The end cell where the formula is in is B4

SPORTS BUDGET 2015 PRIM - Where the list exists

I have an existing formula that works already as follows: =SUMIF('SPORTS BUDGET 2015 PRIM'!A2:A111,$A$4,'SPORTS BUDGET 2015 PRIM'!E$1:E$110)

Basically, on the SUMMARY sheet B4 is looking at the BUDGET sheet, summing all values in column E:E where "SWIMMING" appears in A:A

NOW what I need to do within this is exclude a condition: when "SWIMMING" is found in column A:A and it adds these adjacent amounts, I now need it to ALSO look for two other conditions in column B:B and wherever it finds these I want the adjacent amount to be EXCLUDED. These two conditions are: "COACHING (INDIV)" and "COACHING (COMPANY)"

I hope this is clear enough for somebody to help me with the extra part to this formula (or show me if there is a simpler way to do this)!!

Explanation / Answer

%T6ry,./

=SUMPRODUCT('SPORTS BUDGET 2015 PRIM'!E$1:E$110, ('SPORTS BUDGET 2015 PRIM'!$A$2:$A$111=$A$4)*NOT(SIGN(('SPORTS BUDGET 2015 PRIM'!$B$2:$B$111="COACHING (INDIV)")+('SPORTS BUDGET 2015 PRIM'!$B$2:$B$111="COACHING (COMPANY)"))))

HJust want to point out that the range in column #E is offset one row from the ranges in columns A & B. You might already be aware of that or it could be a typo.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.