We help IT Professionals succeed at work.

If Statement Formula (when it doesnt equal something?)

gisvpn
gisvpn asked
on
Medium Priority
392 Views
Last Modified: 2012-08-12
Hello,

I have these formulas :

=SUMIF(Data!E:E,Summary!F15,Data!J:J)
=SUMIF(Data!E:E,Summary!F16,Data!J:J)
=SUMIF(Data!E:E,Summary!F17,Data!J:J)

How can I do the same thing (total the number in J) but when it does NOT equal Summary!F15,Summary!F16 and Summary!F17 does not equal the value in E?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008

Commented:
Use:

=SUMIF(Data!E:E,"<>"&Summary!F15,Data!J:J)

Kevin
Try the following

=SUMIF(Data!E:E,CONCATENATE("<>",Summary!F15),Data!J:J)

Or Just SUM everything and subtract the results of your sumifs.

Author

Commented:
Hi Kevin,

Thanks for that - how do I add in multiple things that I would like to include i.e. Summary!F15, Summary!F16, Summary!F17 - I tried Ctrl, shift and enter - but was not sure how this worked.

Thanks,

GISVPN
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Try using SUMPRODUCT to exclude multiple values, e.g.

=SUMPRODUCT(ISNA(MATCH(Data!E2:E100,Summary!F15:F17,0))+0,Data!J2:J100)

[best to restrict the range with SUMPRODUCT]

or sum everything and subtract the values the do match like this

=SUM(Data!J:J)-SUMPRODUCT(SUMIF(Data!E:E,Summary!F15:F17,Data!J:J))

regards, barry

Author

Commented:
Ace ;)