MAXIF and MINIF functions with wildcards

[JOOJOHN]

Hi
I have a list of bank transactions in a spreadsheet
for example....

1/7/09 Sainsburys Petrol 34856385
4/7/09 Paypal 92017465
6/7/09 Sainsburys S/Market 38264059
8/7/09 Halfords 93862830
11/7/09 Sainsburys S/Market 34957372
13/7/09 Paypal 92037454
14/7/09 Barclays BCC 173256

and wish to use formulae similar to MAXIF and MINIF to find the last date and the first date of Paypal transactions (for example) if I have just the word "Paypal" in cell D1

I've tried
=SUMPRODUCT(MAX(($B$1:$B$7=$D$1)*$A$1:$A$7))

but obviously this is looking for exact match of "Paypal"

I have successfully used a wildcard in SUMIF functions elsewhere...

eg, $D$1 & "*"

but for some reason
=SUMPRODUCT(MAX(($B$1:$B$7=$D$1 & "*")*$A$1:$A$7))
does not work

Does anyone have any suggestions?
TIA
Joojohn

[squiggler47]

=MAX((LEN(SUBSTITUTE(LOWER(B5:B6),"paypal",""))<>LEN(B5:B6))*C5:C6)

would work for MAX, min is a little more involved!

=MIN(($C$5:$C$8+(1.1E+300*(LEN(SUBSTITUTE(LOWER($B$5:$B$8),"paypal",""))=LEN($B$5:$B$8)))))

both confirmed with CSE

[JOOJOHN]

Doesnt seem to be working

in your example, does B5:B6 and C5:C6 correspond to my list?
where B5:B6 is B1:B7
and C5:C6 is A1:A7

A B
1 1/7/09 Sainsburys Petrol 34856385
2 4/7/09 Paypal 92017465
3 6/7/09 Sainsburys S/Market 38264059
4 8/7/09 Halfords 93862830
5 11/7/09 Sainsburys S/Market 34957372
6 13/7/09 Paypal 92037454
7 14/7/09 Barclays BCC 173256

[squiggler47]

Yes, but did you confirm the formulas with Ctrl-Shift-Enter?

[DonkeyOte]

First Date

=MIN(IF(ISNUMBER(SEARCH($D$1,$B$1:$B$7)),$A$1:$A$7))
committed with CTRL + SHIFT + ENTER

for Last Date as above but switch MIN to MAX

[JOOJOHN]

thanks for those,

squiggler, your max formula will only work for numerical text (tried it with a cheque number)

DonkeyOte, your max formula works perfectly thanks! but the min formula will only return the first date in the list
eg 1/7/09 rather than 4/7/09

[DonkeyOte]

DonkeyOte, your max formula works perfectly thanks! but the min formula will only return the first date in the list
eg 1/7/09 rather than 4/7/09

Only if you have a transaction containing Paypal in B that is dated 1st July, based on your sample data it will return 4th July.

Did you confirm both MAX & MIN as Arrays with CTRL + SHIFT + ENTER

Might be an idea to post a sample file with formulae in place.

[JOOJOHN]

heres a cut down version of my sheet

[DonkeyOte]

Your MIN functions are referencing C and not B and C is blank.

Given C is blank it will simply return the MIN date of the entire range given the SEARCH will return a number for each row in the array.

[JOOJOHN]

Oh yeah, schoolboy error there, I dragged the formula right to do the MIN function without locking to Column B, thanks!

[JOOJOHN]

Just one more question...
I am using
{=MIN(IF(ISNUMBER(SEARCH($D$1,$B$1:$B$7)),$A$1:$A$7))}
and
{=MAX(IF(ISNUMBER(SEARCH($D$1,$B$1:$B$7)),$A$1:$A$7))}
but how can I only return a max or min value if the text in D1 is found only in the first position of a cell by the search function
eg,
{=MIN(IF((SEARCH($D$1,$B$1:$B$7)=1),$A$1:$A$7))}
have tried this with no luck

(all array entered)

[martindwilson]

=MAX(IF(ISNUMBER(SEARCH(D1,$B$1:$B$25))*SEARCH(D1,$B$1:$B$25)=1,$A$1:$A$25))
works(array entered)

[daddylonglegs]

For the MIN value where column B begins with the value in D1 You can use LEFT function, i.e.

=MIN(IF(LEFT($B$1:$B$7,LEN($D$1))=$D$1&"",$A$1:$A$7))