Using Filter string with IF function

[T. Valko]

Maybe this:

Assuming row 1 are the column headers. This array formula** will return the
*first* filtered (or unfiltered) record from column C.

=INDEX(C2:C15,MATCH(1,(SUBTOTAL(3,OFFSET(C2:C15,ROW(C2:C15)-MIN(ROW(C2:C15)),0,1)))*(C2:C15<>""),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Aref]

Is there a way to use a filter string with the IF function?
I have a spreadsheet that looks like this:

Invoice No. Invoice Date Invoice Currency Invoice Value
1 dd/mm/yyyy USD 1234.56
65 dd/mm/yyyy USD 2345.67
74 dd/mm/yyyy EUR 3456.78
88 dd/mm/yyyy USD 4567.89

when I filter on the currency: is there a function such as
if(filter_string="USD";TRUE;FALSE)???

Thanks
Aref

 

[MartinW]

Hi Aref,

Biff's formula works fine, you just didn't array enter it.
Take a look at the last bit of his post.

These 2 formulae will return the sum of USD and EUR respectively.

=SUMPRODUCT(--(C2:C5="USD"),(D25))

=SUMPRODUCT(--(C2:C5="EUR"),(D25))

You can put them in one big IF statement but it is probably
better to put them in separate cells and then reference
all 3 cells in your IF formula. For example

With Biffs formula in C16 and the other two in say M16 and M17,
Put something like this in D17.
=IF(C16="USD",M16,M17)

HTH
Martin

 

[T. Valko]

formula works fine, you just didn't array enter it.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key.With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formulas in the brackets. You
can't just type these brackets in, you *must* use the key combo. Also,
anytime you edit an array formula it *must* be re-entered as an array using
the key combo.

So, to array enter that formula:

Select cell C16
In the formula bar, place the cursor at the end of the formula
Hold down both the CTRL key and the SHIFT key then hit ENTER

=If ("filter"="USD";sum all USD invoices values;if("filter"="EUR";sum all
EUR invoices values;""))

You can just use the SUBTOTAL function.

=SUBTOTAL(9,D25)

When the data is unfiltered ("show all") it will sum the entire range. When
it's filtered on the Currency column on USD then the sum will only be for
USD. When it's filtered on the currency column on EUR then the sum will only
be for EUR. If the data is filtered on a different column, like Date, then
you'll need a different formula.

 

[Aref]

Thanks agaian..... This is working fine now, but when no filter is set it is
returning the currency to "USD"!!!
When no filter is set the formula should show a BLANK cell or Display "NO
FILTER SET".
Is this possible???

 

[T. Valko]

Yeah, it's possible but the resulting formula will be really long and
complicated!

I'll write it up tomorrow. I'm calling it a day for right now!

 

[Aref]

Great job guys... Thanks a lot.

Actually, the formula isn't all that complicated or long!

Array entered** :

=IF(COUNTA(C2:C15)=SUBTOTAL(3,C2:C15),"",INDEX(C2:C15,MATCH(1,(SUBTOTAL(3,OFFSET(C2:C15,ROW(C2:C15)-MIN(ROW(C2:C15)),0,1)))*(C2:C15<>""),0)))

 

[T. Valko]

When no filter is set the formula should show a BLANK cell or Display "NO

Yeah, it's possible but the resulting formula will be really long and
complicated!

Actually, the formula isn't all that complicated or long!

Array entered** :

=IF(COUNTA(C2:C15)=SUBTOTAL(3,C2:C15),"",INDEX(C2:C15,MATCH(1,(SUBTOTAL(3,OFFSET(C2:C15,ROW(C2:C15)-MIN(ROW(C2:C15)),0,1)))*(C2:C15<>""),0)))

 

[Roger Govier]

Hi

Following on from Biff's later suggestion about using the Subtotal function,
you could combine that with a simple Function to show whether Filter is set.

Function FilterOn()
If ActiveSheet.FilterMode = True Then
FilterOn = 1
Else
FilterOn = 0
End If
End Function

=SUBTOTAL(9,D25)*FilterOn()
will return 0 when no filter is set.
If you wanted it to be blank, then
=IF(FilterOn,SUBTOTAL(9,D25),"")

To use the Function, copy from above
Alt+F11 to invoke the VB Editor
Insert>Module>paste the function into the white pane that appears on screen.
Alt+F11 to go back to your Excel sheet.

 

[T. Valko]

You're welcome. Thanks for the feedback!