SUMIF

  • Thread starter Thread starter Khalil Handal
  • Start date Start date
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.
 
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
 
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
 
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
 
Back
Top