if then count sum

[Ann]

i need to count the sum of values in column A if column B <> "sent" ... sent
is text written into the cell
thanks in advance.

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

=SUMIF(B1:B200,"<>sent",A1:A200)

Adjust the ranges to suit your needs.

Rick

 

[Ann]

had a follow up.

if column C is one of 4 statements. how do i use both?
for example, if column c is "A" then SUMIF(B1:B200,"<>sent",A1:A200)

 

[Rick Rothstein \(MVP - VB\)]

For future reference, you should always ask the question you want an answer
to, not a simplified version of it... the answers to them will almost always
be different. I'm not sure what you mean by "C is one of 4 statements", but
going with your example of Column C containing an "A" and Column B does not
contain "sent", this formula will count add up the values from Column A
where both of those statements are true for the same row...

=SUMPRODUCT((B1:B200<>"sent")*(C1:C200="A")*A1:A200)

Rick

 

[Bob Phillips]

Do you mean

=IF(OR(C2="A",C2="B",C2="C",C2="D"),SUMIF(B1:B200,"<>sent",A1:A200),"")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ann]

sorry you are correct, i have misposted this question in this thread and in
the other one i started. i apologize and thank you for your help.

=SUMPRODUCT(--(H3:H200="AA 250"),--(F3:F200<>"sent"),E3:E200)

i want to add in "AA 250" or "AA 251" for this statement as the sum of these
should be together, the rest I've got.

thanks again.

 

[Ann]

ok, i've got it, thank you so much.

sorry you are correct, i have misposted this question in this thread and in
the other one i started. i apologize and thank you for your help.

=SUMPRODUCT(--(H3:H200="AA 250"),--(F3:F200<>"sent"),E3:E200)

i want to add in "AA 250" or "AA 251" for this statement as the sum of these
should be together, the rest I've got.

thanks again.

 

[Bob Phillips]

=SUMPRODUCT(--((H3:H200="AA 250")+(H3:H200="AA
251")),--(F3:F200<>"sent"),E3:E200)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[David Biddulph]

A minor point, but are you sure that you need the double unary minus on the
column H term? Doesn't the addition do the job of coercing to a number?

 

[SwearBear]

David,

What the heck is a "double uanry minus" and what does it do?

 

[Dave Peterson]

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html