SUMIF

[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.

 

[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

 

[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

 

[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

 

[Khalil handal]

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