Sumif and with a filter?

[lky2k23]

I am trying to do a sumif

=SUMIF(O:O,"<>do not invoice",L:L)

but i am using filters as well, how can i get this added in as to not show the unfiltered data as well?

thanks

[Fotis1991]

Hi

Which are filter conditions??
Why don't you use ranges. EX.>>o1:o50>>>>l1:l500
Maybe you need Subtotal function.
Would you like to upload a small sample workbook?

[lky2k23]

thanks - attached.

[Marcol]

Try using
=SUBTOTAL(109,"range to sum")

Then filter out "do not invoice" in addition to your other conditions.

[Fotis1991]

Maybe =SUBTOTAL(9;L:L)

[lky2k23]

Thanks I had hoped there would have been a way to avoid that but i dont need to see the do not invoices anyway.

[OnErrorGoto0]

I think you would need a formula like

=SUM(IF($O$2:$O$1658<>"Do not invoice",IF(SUBTOTAL(3,OFFSET($L$2:$L$1658,ROW($L$2:$L$1658)-MIN(ROW($L$2:$L$1658)),,1)),$L$2:$L$1658,0)))

array-entered.

[lky2k23]

#value!

not sure why

[OnErrorGoto0]

Did you enter it with Ctrl+Shift+Enter?

[lky2k23]

no just ctrl+v

tried c+s+e same thing

[OnErrorGoto0]

Select the cell with the formula, press f2, then Ctrl+Shift+Enter. Works in the sample you posted.

[lky2k23]

perfect!

what does C+S+E do?

another add on to this, is there anyway I can get (either by adding to my refresh macro) what is in the filter copied to the 'page' of my pivot table?

[OnErrorGoto0]

Ctrl+Shift+Enter makes it an array formula.

Not entirely sure what you are referring to with that last question?

[lky2k23]

If you can see macro 3, I would like to add what it pastes to the front sheet A1, to the pivot 'page' (C1) When i have tried to paste this in i get an error you cannot change, move a part of........so I have to manually enter this in

Thanks

Attached.

[OnErrorGoto0]

I think perhaps you mean something similar to this?

Please Login or Register  to view this content.

[lky2k23]

should i just replace my macro 3 with this?

[OnErrorGoto0]

Yes - it should do all of the same things I think plus setting the filter on the pivot table.

[lky2k23]

FANTASTIC! You saving me hours!

Hopefully last thing, I want to be able to print 2 pages to pdf is this possible? I have got the following;

Sheets(Array("Front Sheet £", "Backup")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF", Collate:=True

But it prints to 2 documents currently. What would be really cool is if it took the name to save it as the company name on the front sheet but 1 step at a time

[OnErrorGoto0]

I am afraid I know next to nothing about printing to PDF, other than that it is much easier on 2007+.

[lky2k23]

thanks for all your help.