Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT help
I'm trying to use the SUMPRODUCT trick to count occurrences of multiple
match criteria. With simple conditions like checking if a cell in a range is 0, the trick works fine. Even with LEFT, MID functions it works fine... A2 = 1st criteria B2 = 2nd criteroa B50:Q800 = range of cells to search =SUMPRODUCT(--(LEFT($B$50:$Q$800,1)=TEXT($A2,"0")),--(MID($B$50:$Q$800,3,3)=LEFT($B2,3))) WORKS <<< What I can't get to work is if I want to use the FIND (or SEARCH) as one of the criteria to see if each cell contain the search text: =SUMPRODUCT(--(FIND($B2,$B50:$Q800)0)) DOESN'T WORK <<< Are there only certain functions that work with the SUMPRODUCT trick? Any Ideas on how to do a search text as part of a SUMPRODUCT criteria? Thanks! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |