# Posts by Robert Mika

• ## Multiple Criteria for Max

Re: Multiple Criteria for Max

What kind of data do you test?

• ## Return Value Depending On Text In Cell -Second Question

Re: Return Value Depending On Text In Cell -Second Question

B28:
=IFERROR(VLOOKUP(A28,Sheet1!\$A\$10:\$B\$52,2,0),"")

• ## More Sum If Help Please

Re: More Sum If Help Please

Also
=SUM(IF(\$A\$2:\$A\$20="mex",IF(B\$2:\$B\$20>0,\$B\$2:\$B\$20)))
Confirm Control+shift+Enter

• ## Multiple Criteria for Max

Re: Multiple Criteria for Max

Quote from pike;681733

just doing a little more testing and it has half the evaluations in its processing

So - is it time to change?

• ## Search for any character contained in a given cell on another cell

Re: Search for any character contained in a given cell on another cell

Could you post an example in workbook?

• ## More Sum If Help Please

Re: More Sum If Help Please

Are you using Excel 03 or higher version?

• ## Search for any character contained in a given cell on another cell

Re: Search for any character contained in a given cell on another cell

=SUM(--ISNUMBER(SEARCH({"a","b","c"},A1)))
Confirm Control+Shift+Enter
Add more values separeted by coma.

• ## Multiple Criteria for Max

Re: Multiple Criteria for Max

No, not this one.
But I hope this clarifies some uncertainty

• ## Return Value Depending On Text In Cell -Second Question

Re: Return Value Depending On Text In Cell -Second Question

=VLOOKUP(E\$2,Sheet2!\$X\$2:\$Y\$10,2,0)
If your lookup value is on one sheet and the table on another

• ## Multiple Criteria for Max

Re: Multiple Criteria for Max

I do not have link but quoted Aladin Akyurek( I belive you know that name)
A function call means some overhead. So many thinks: the lesser the number of such calls the faster the calculation. But I opt for because I expect filtering to be faster than pairwise multiplications (+ INDEX).

Since you just have 3 IFs, I'd go for the first formula for reasons of efficiency and transparency.

• ## Match names & other data from two spreadsheets

Re: Match names &amp; other data from two spreadsheets

The possible soultions:VLOOKUP or INDEX/MATCH
If you have two Spreadsheets the best way is first to marge them into one.

• ## Multiple Criteria for Max

Re: Multiple Criteria for Max

Actually did not time myself but there was a topic on MrExcel where some of the gurus recemmended to us IF() function instead of multiplication.

• ## Multiple Criteria for Max

Re: Multiple Criteria for Max

This should be slighlty quicker:
=MAX(IF(\$C\$1:\$C\$289774=M1,IF(\$D\$1:\$D\$289774=N1,IF(\$E\$1:\$E\$289774,\$I\$1:\$I\$289774)))

Confirm Control+Shift+Enter

You are welcome.
Fallow with this formula for your other list(s).

• ## Data Validation (List) automatically changing my list to dates

Re: Data Validation (List) automatically changing my list to dates

Code
``````Sub M_snb()
Range("A1").NumberFormat = "@"
End Sub``````
• ## Data Validation (List) automatically changing my list to dates

Re: Data Validation (List) automatically changing my list to dates

Ae those data created by macro or they are coming from a list on a sheet?
Have you tried to use Named Ranges?

• ## SUMIF & SUM with multiple Criteria

Re: SUMIF &amp; SUM with multiple Criteria

=SUMPRODUCT((D6:D53>=B2)*(D6:D53<=B3)*(G6:G53="PROFESSIONAL FEES DR. CHANDI RAM")*(J6:J53<>"OSR")*(K6:K53="HMEM")*(I6:I53))
You can add another criteria to your array formula or as you the format you posted is xlsx you can use SUMIFS()

• ## COUNTIFS for multiple criteria in multiple columns

Re: COUNTIFS for multiple criteria in multiple columns

Your last range is E:AI which COUNTIFS can not handle.
Try to play with SUMPRODCT.