SUMPRODUCT

B

brian ferris

Hi there,

I am trying to use this formula which I tried and worked
for several times... but somehow is producing a VALUE
error now.

Eg.

A Brian 20
B Brian 40
A Brian 50
A Brian 60
B Ivan 70

I want to produce a total if 1st column = B, and second
column = Ivan, giving me a total of just 70 cause there is
one corresponding entry. The formula I previously used was:

sumproduct((a1:a100="B")*(b1:b100="Ivan")*(c1:c100))

Somehow this formula is not working anymore. I know it can
be done by an array but I know how to insert only one
condition into the array and not two.

Please advise any suggestions.

Thanks
 
F

Frank Kabel

Hi
sounds like you have text valus in column C 8maybe a
heading in row 1.) Try the following formula
sumproduct((a1:a100="B")*(b1:b100="Ivan"),c1:c100)

or
sumproduct(--(a1:a100="B"),--(b1:b100="Ivan"),c1:c100)
 
B

Bob Umlas

Also -- check down column C and see if there's already a #VALUE error
somewhere, which would be replicated in the result of the SUMPRODUCT
formula.

Bob Umlas
Excel MVP
 
Top