Filtering and conditions !

[Khalil Handal]

Posted before but without any solution!!!
Expensesmonth in Column B is the range B7:B800
Expensescode in Colomn K is the range K7:K800
As in the table below:
I filter colomn B for the month of August (Aug) for example and after this
:
I want to have a formula in cell H3 to sum all the values in the range
H7:H800
with except (not including) the line with the codes 888 or 999.

In other words:
Filter Month then filter Code were it is not equal to 888 or 999 and then
sum the remaining numbers in colomn H.
Hope this is clear.

B C H K
Month Date numbers Code
Jun 14/06/08 60 121
Jun 14/06/08 180 56
Jul 31/07/08 999
Jul 31/07/08 11
Aug 29/08/08 176 95
Aug 30/08/08 16
Sep 01/09/08 615 888
Sep 02/09/08 359 62
Sep 17/09/08 122.5 94

 

[Per Jessen]

Hi

This nice little formula will do it, no filtering required.
In G2 you enter the month to sum (eg. Aug).

=SUMPRODUCT(--(B7:B800=G2),--(K7:K800<>888),--(K7:K800<>999),H7:H800)

Best regards,
Per

 

[Jon von der Heyden]

Hi Khalil

Try:

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($H$7:$H$800,1,1),ROW($H$7:$H
$800)-ROW(INDEX($H$7:$H$800,1,1)),0))=1),--($K$7:$K$800<>999),--($K
$7:$K$800<>888),$H$7:$H$800)

Regards
Jon von der Heyden

 

[Khalil Handal]

Thanks for both of you. Jon 's Formula works better for me since I don't
need to put the value in G2 each time.

Thanks for Both of you again.