SUMIF

K

Khalil Handal

This formula works well:
=SUMIF(expensecode,"<>999",E8:E19) formula 1

I tried to add another criteria as follows: always have a zero answer:
=SUMIF(expensecode,OR("<>999","<>888"),E8:E19) formula 2

Expense code is the name range of G8:G19
Can anyone help to make the second formula work.
 
P

Pete_UK

You could do it like this:

=SUM(E8:E19) - SUMIF(expensecode,"999",E8:E19) -
SUMIF(expensecode,"888",E8:E19)

or you could try this approach:

=SUMPRODUCT((expensecode<>"999")*(expensecode<>"888)*(E8:E19))

Hope this helps.

Pete
 
P

Pete_UK

Sorry, I just spotted a missing quote on the second formula after 888.

Actually, though, you only need the quotes in both formulae if your
expenses codes are text values. If they are not then you can omit the
quotes entirely.

Hope this helps.

Pete
 
K

Khalil handal

Hi Pete,
It does not work when filtering. Can it be adjusted for this???
Both formulas worked well without filtering.
Thanks for the info.


Sorry, I just spotted a missing quote on the second formula after 888.

Actually, though, you only need the quotes in both formulae if your
expenses codes are text values. If they are not then you can omit the
quotes entirely.

Hope this helps.

Pete
 
K

Khalil handal

Forgot to mention this:
The filtering will be for the values in the range E8:E19
 
Top