To sum with 2 criteria

[JoeC]

I would like to use a formula, that can be filled out,
and which will add amounts in col H provided two criteria
are met. The first criteria is a date in col B, and the
second criteria is a description in Col C. Can you
suggest anything please?
Regards
JoeC

 

[Don Guillett]

try, where b1 has your date
=sumproduct((b2:b200=b1)*(c2:c200="mydescription")*h2:h200)

 

[Norman Harker]

Hi JoeC!

Try the following approach:

=SUMPRODUCT(--(($B$1:$B$200)=DATE(2004,7,7)),--(($C$1:$C$200)="Salary"),($H$1:$H$200))

The -- coerce the returns of the expressions from TRUE or FALSE to 1
or 0.

Better to substitute a cell with the date in it for the DATE function.

 

[JoeC]

Sorry Norman & Don, but I can't get it to work. Since I
need to add only those amounts in col H that relate to a
certain date and description, should I be looking at
nesting SumIF(AND although I have tried without success.
I can't see how Sumproduct can work. Please don't give up
on me just yet.
Best Regards
JoeC

 

[Norman Harker]

Hi JoeC!

I can assure you that both Don and my solutions work, although Don's
will fail if H2:H200 contains text (and will add Booleans). SUMIF has
the limitation of not being able to handle multiple criteria.

However, SUMPRODUCT won't accept full column arguments (eg) B:B. You
must use (eg) B1:B1000

If that isn't the problem, you'll need to post a sample of the data
you have put in columns B, C and H so that we can see what's up.

As to how SUMPRODUCT works. In essence we are using returns of 1 and 0
for the conditions in B and C and multiplying the results by your data
in column H. Only if both conditions are TRUE (1) will you get a
non-zero answer. SUMPRODUCT sums the products of those answers. If one
or both condition is FALSE (0) then 0 is added. If both conditions are
TRUE (1) then the amount in H gets added.

 

[RagDyer]

<<"although Don's will fail if H2:H200 contains text">>

Is the connotation there Norman, that yours will add text?

The unary *ain't* that powerful!<bg>

However, while *both* will fail with alpha text, *only* the unary fails with
numeric text, which is why I've always favored the "old" style.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hi JoeC!

I can assure you that both Don and my solutions work, although Don's
will fail if H2:H200 contains text (and will add Booleans). SUMIF has
the limitation of not being able to handle multiple criteria.

However, SUMPRODUCT won't accept full column arguments (eg) B:B. You
must use (eg) B1:B1000

If that isn't the problem, you'll need to post a sample of the data
you have put in columns B, C and H so that we can see what's up.

As to how SUMPRODUCT works. In essence we are using returns of 1 and 0
for the conditions in B and C and multiplying the results by your data
in column H. Only if both conditions are TRUE (1) will you get a
non-zero answer. SUMPRODUCT sums the products of those answers. If one
or both condition is FALSE (0) then 0 is added. If both conditions are
TRUE (1) then the amount in H gets added.

 

[Terri Carlson]

Hi,

It's called a "If/Then" statement. It is under your
Excel functions. (See Excel help if need be.)

The formula will go something like this:

If(B1=7/1/1999,If(C1="Sale",H1,0),0)

Good luck!

 

[Harlan Grove]

<<"although Don's will fail if H2:H200 contains text">>

Is the connotation there Norman, that yours will add text?

The unary *ain't* that powerful!<bg>

However, while *both* will fail with alpha text, *only* the unary
fails with numeric text, which is why I've always favored the "old"
style.

....

All a question of whether you want SUMPRODUCT constructs to function like
SUM and SUMIF or like +. If SUM({1;"2";3}) == 4, why would it make sense for
SUMPRODUCT(({1;"2";3}>0)*{1;"2";3}) == 6?

 

[RagDyeR]

It's not what I "want" Harlan, it's just what "is", or more correctly, what
Redmond says "is" should be.

I simply made an observation, stated a fact, and then voiced a preference.
A preference based on the operations of our offices importing a large amount
of pricing data, which sometimes is text, and sometimes is not.

The fact that the unary returns zero while the "old style" (how would you
label that Harlan) returns an error message, is another "Plus" in my book
for the "old style", since the zero doesn't really let you know if it
*really means* zero, or look at your data for "maybe" something wrong.

But, I'm sure not looking to debate you on this subject of which form is
better or worse.

Just stating an opinion.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

<<"although Don's will fail if H2:H200 contains text">>

Is the connotation there Norman, that yours will add text?

The unary *ain't* that powerful!<bg>

However, while *both* will fail with alpha text, *only* the unary
fails with numeric text, which is why I've always favored the "old"
style.

....

All a question of whether you want SUMPRODUCT constructs to function like
SUM and SUMIF or like +. If SUM({1;"2";3}) == 4, why would it make sense for
SUMPRODUCT(({1;"2";3}>0)*{1;"2";3}) == 6?

 

[Norman Harker]

Hi RD!

What I meant was that if you have text in the range to be summed, the
* format will resolve to #VALUE! where the -- will treat it as 0. Also
if you have a Boolean TRUE in the range to be summed, this will be
included in the sum.

Now we could debate whether or not #VALUE! is a preferable return with
text in the range and similarly debate whether the Boolean 1 should be
added if the other conditions are true. I'll go for Excel's treatment
but listen with interest to alternative views.

 

[RagDyeR]

Hi Norm,
As I mentioned to Harlan, I'm not looking for any debates, primarily because
I really can't hold a candle to you guys when it comes to these technical
issues.

I simply "threw" a little "dig" at you for fun<g>, and, repeating what I
stated above,
<<"I simply made an observation, stated a fact, and then voiced a
preference.">>

We all know that there *isn't* anything in XL that can only be done in one
way.

In fact, that may be a good thread to start, and see if there *is* anything.

It sure would never be as large as yours on the FOURTH !

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi RD!

What I meant was that if you have text in the range to be summed, the
* format will resolve to #VALUE! where the -- will treat it as 0. Also
if you have a Boolean TRUE in the range to be summed, this will be
included in the sum.

Now we could debate whether or not #VALUE! is a preferable return with
text in the range and similarly debate whether the Boolean 1 should be
added if the other conditions are true. I'll go for Excel's treatment
but listen with interest to alternative views.

 

[Norman Harker]

Hi RD!

Hope you didn't take a fence! I thought you had mis-interpreted the
reason for my (poorly stated) preference.

4th July will be repeated on Columbus Day?

 

[Ragdyer]

I'm originally from N.Y., where *only* the Italians appreciated that
holiday, and the Parade down Fifth Avenue!