Sumproduct formula needed

  • Thread starter Thread starter K
  • Start date Start date
K

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
 
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))
 
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.
 
Back
Top