SUMPRODUCT question

Hi,

I have the following formula:

=SUMPRODUCT(('Data Table 1'!$D$7:$D$1311=A5)*(ISNUMBER(FIND("A",'Data Table 1'!$F$7:$F$1311))))

I need to modify the formula so that it looks like the following:

=SUMPRODUCT(('Data Table 1'!D7:D100="Unbalanced*")*(ISNUMBER(FIND("*A*",'Data Table 1'!F7:F100))))
I tried doing it myself but all I got was a value of 0

How do I make the above formual work?

Thanks in advace for any help.

Calli

You shouldn't need the *s around "A". FIND looks for "A" anywhere in the text anyway, try using LEFT function on the first criterion like this

=SUMPRODUCT((LEFT('Data Table 1'!D7:D100,10)="Unbalanced")*ISNUMBER( FIND("A",'Data Table 1'!F7:F100)))

Hi,

The reason I used the ("Unbalanced*) and (*A*) was because in the column that I looking at - there are some cells that start off with Unbalanced and have other comments afterwards.

Also, in the second column that I am looking at - there is such data like the following: A/B or B/A

I also tried what you suggested above, but it didn't work. Do you have any other suggestions?

Calli

It works for me

The formula I suggested counts rows where the D column entry starts with "unbalanced" and the F column contains an upper case "A" (anywhere in the cell). Is that what you want to count?

What result did you get, an error or an incorrect count?

Hi,

I not getting the same results, the number are just not correct.

I put a zip file together to show you what I have to work with.

Let me know if this is doable or not?

Thanks

Calli

Given that example perhaps try this formula in F6 copied across and down

=SUMPRODUCT(($A$2:$A$37=$E6)*(LEFT($B$2:$B$37,LEN(F$5))=F$5))

Thanks,

The majority of the numbers worked out, but the cells that have values like B/B/C are captured in such away that the C is not captured and the B/B is captured as a count of 1.

Is there any way to get past this?

Calli.