Sumproduct multiple criteria

[EvaCheng0404]

I want to use sumproduct with multiple criteria.
I have a cell A1 to enter a text e.g. aaa or any text.
How can I use mid function to find cells that refer to A1 in sumproduct formula.

Thanks
Eva

 

[hagia_sofia]

I am not sure whether you are looking for a partial match (i.e. the cell contains a certain text) or complete match (the cell is equal to a certain text) but something like this?

= SUMPRODUCT((ISNUMBER(FIND("AAA",A1:A5)))*(B1:B5))

=SUMPRODUCT((A1:A5="AAA")*(B1:B5))

 

[EvaCheng0404]

I am not sure whether you are looking for a partial match (i.e. the cell contains a certain text) or complete match (the cell is equal to a certain text) but something like this?

= SUMPRODUCT((ISNUMBER(FIND("AAA",A1:A5)))*(B1:B5))

=SUMPRODUCT((A1:A5="AAA")*(B1:B5))

Thanks for your quick response

However, my formula as below that criteria 1 & 2 works.
But for criteria 3 I want to sum any record that has the text in P5 in J column, it returns 0
The formula only search the 1st-3rd records that match P5
=SUMPRODUCT(--($E$10:$E$10000=$P$1),--(LEFT(H$10:$H$10000,4)=$P$2),--(mid($J$10:$J$10000,1,3)=$P$5),$L$10:$L$10000)

 

[hagia_sofia]

Are numbers or letters in column J or both?

 

[EvaCheng0404]

Are numbers or letters in column J or both?

yes the searching criteria is within the text

 

[EvaCheng0404]

yes the searching criteria is within the text

both

 

[hagia_sofia]

What is it that is in P5? If it is a text string, then you should go without double negative.

 

[EvaCheng0404]

Cell P5 : 212

J column
AW212xxxxx
212Awxxxxx
AAA212xxxx

L
1
2
3

If I use sumproduct(--(1st criteria),--(2nd criteria),--(Mid(J1:J3,1,3)=P5, L1:L3)

The formula returns 2 not 6

 

[hagia_sofia]

I see. So test this:

=ISNUMBER(FIND(P5,J1:J3))

or this inside your formula:

--(ISNUMBER(FIND($P$5,$J$10:$J$10000)))

 

[EvaCheng0404]

I see. So test this:

=ISNUMBER(FIND(P5,J1:J3))

or this inside your formula:

--(ISNUMBER(FIND($P$5,$J$10:$J$10000)))[/CODE
[/QUOTE]

I see. So test this:

=ISNUMBER(FIND(P5,J1:J3))

or this inside your formula:

--(ISNUMBER(FIND($P$5,$J$10:$J$10000)))

Thanks
How about if cell P5 ：AW