To sum with 2 criteria

  • Thread starter Thread starter JoeC
  • Start date Start date
J

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
 
try, where b1 has your date
=sumproduct((b2:b200=b1)*(c2:c200="mydescription")*h2:h200)
 
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.
 
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
 
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.
 
<<"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.
 
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!
 
RagDyer said:
<<"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.
Click to expand...
....

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?
 
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 !
--------------------------------------------------------------------


RagDyer said:
<<"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.
Click to expand...
....

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?
 
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.
 
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.
 
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?
 
I'm originally from N.Y., where *only* the Italians appreciated that
holiday, and the Parade down Fifth Avenue!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find largest value in column with 2 criteria 1
Sum total using Sumproduct if two criteria met 5
SUMIF and AND/OR/NOT 7
Count multiple criteria 7
IF and OR Function 3
Counting number of rows based on mutiple criteria 2
SUMIF FUNCTION 5
Sum for two criteria 3

Back
Top