SUMPRODUCT and IF statement

Question

Hi, I need a complicated formula. I currently have a excel sheet that produces a number or tables dependant on the main sheets entries. To do this i use SUMPRODUCT formula:-

=SUMPRODUCT(('Week 1'!C:C="RED")*('Week 1'!I:I="GREEN"))

What i want to add though as part of the calculation is to look through column I and say work out entries that do not equal GREEN, BLUE or YELLOW. So this will then show up everything else. Is this possible?

Answer 1

This is really good. Is there anyway to also include blanks? At the moment the formula picks up 8 of the 10 other entries that do not equal those colours. The two entries that are missing are blank in column I

Answer 2

Hi,

Since you have only three colours to exclude, try this

=SUMPRODUCT(('Week 1'!C:C="RED")*(('Week 1'!I:I<>"GREEN")+('Week 1'!I:I<>"BLUE")+('Week 1'!I:I<>"YELLOW"))

Also, avoid using entire columns as references.  Use specific data ranges instead i.e. 'Week 1'!$I$2:$I$500.

Hope this helps.

Answer 3

Hello Daniel,

Try this.

=SUMPRODUCT(('Week 1'!C2:C1000="Red")*ISNA(MATCH('Week 1'!I2:I1000,{"Green","Yellow","Blue"},0)))

Do not use whole column reference. as they will slow down the calculation.

Since you are on Excel 2007, you could also use COUNTIFS

=COUNTIFS('Week 1'!C:C,"Red",'Week 1'!I:I,"<>Green",'Week 1'!I:I,"<>Blue",'Week 1'!I:I,"<>Yellow")

EDIT:

This will also count if column c = Red 7 Column i is BLANK, so if you don't want to count blank add one more condition C2:C100<>"", in COUNTIFS I:I,"<>"

Answer 4

I have managed to get it to filter one option out but not multiple.

=SUMPRODUCT(('Week 1'!C:C="RED")*('Week 1'!I:I<>"GREEN"))

I want it to extend like this

=SUMPRODUCT(('Week 1'!C:C="Implementation")*('Week 1'!I:I<>"TT1 ZSERIES""YELLOW""BLUE"))

But it doesn't seem to filter it down correctly. Instead of filtering down to show an answer of 8 other entries, it shows that there were 249 entries which is the overall total.