Sumproduct formula needed

[K]

Data************************

A B…..col
001 40000
002 25000
999 3200
005 11360
920 -3000
992 -165870
170 3290
210 31090
991 -9220

*******************************

Hi all, I need Sumproduct formula in cell C1 which should check those
values in column A which starts with 9 and then do the SUM of values
in column B. I tried formula (see below) but its not working.

SUMPRODUCT((A1:A9=9**)*(B1:B9))

I can solve it by SUMIF formula but I want to do it in SUMPRODUCT
formula. Please can any friend help me on this

 

[Roger Govier]

Hi

If they are numbers in column A, then you can't use 9** as the test.
Try
SUMPRODUCT((A1:A9>=900)*(A1:A9<=999)*(B1:B9))

 

[Mike H]

Hi,

You can't use wildcars in Sumproduct so try it like this

=SUMPRODUCT((ISNUMBER(FIND("9",LEFT(A1:A9,1))))*(B1:B9))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.