SUMIF Question

B

Barb Reinhardt

I want to sum the values in cells E2:p110 based on the values column D. The
values in D are formulas resulting in something that appears to match D112 in
some cases. I'm using the following equation:

=SUMIF(D2:D110,D112,E2:p110)

My problem is that D2 :D10 have a formula in it and it's not matching. If
I enter the result of the formula, all is good. How should I deal with this?

Thanks in advance,
Barb Reinhardt
 
T

Tim C

Barb,

It sounds like a rounding issue. What you see displayed may not be the
exact value of the cell. Try modifying your formulas to round the value of
the cell, not just the display.

Tim C
 
P

Pete_UK

Hey Bob, I've not seen this symbol before - have you been able to
attach something here? I'm viewing through Google Groups.

Pete
 
B

Bob Phillips

Pete,

Do you mean that card type icon in the top-right corner? If so, that is a
personalised business card. I was playing with Mozilla Thunderbird
newsreader, and added one. Just trying it out.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Barb Reinhardt

Bob,

Let's say I want to concatenate B2 with D2 and compare it to B114, what do I
change? Or do I need to have a helper column?

=SUMPRODUCT((D2:D110=B114)*(E2:p110))
 
B

Barb Reinhardt

Bob, how would I count how many values in E2:p110 have values where the D
column values match D112.
 
B

Bob Phillips

No, you can do that

=SUMPRODUCT((B2:B110&D2:D110=B114)*(E2:p110))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

Just test the range rather than multiply it

=SUMPRODUCT((D2:D110=B114)*(ISNUMBER(E2:p110)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

Aladin Akyurek

Better switch to a Sum/If formula when a vector and a matrix involved:

=SUM(IF(D2:D100=D112,E2:p100))

which needs to be confirmed with control+shift+enter.

In fact, it's not unreasonable to create a total per record in an
aditional column and invoke an ordinary Sumif formula:

Q2, copied down:

=SUM(E2:p2)

Then:

=SUMIF(D2:D110,D112,Q2:Q110)
 
Top