SumIf Function

[Guest]

I'm trying to use the SumIf Function (as I understand its use) and I keep
running into the same problems -- in many cases I have more than one
criterion for addind a specific cell. Is there a way to modify SumIf to
allow for multiple criteria? I try to do it in the insert function box and
it gets spit back out at me. Any ideas?

Jay

 

[Trevor Shuttleworth]

Jay

look at SUMPRODUCT

Look at the Help and search the Archives for examples

Regards

Trevor

 

[Don Guillett]

=sumproduct((a2:a22="Jay")*(b2:b22=1))
to sum c based on that criteria

=sumproduct((a2:a22="Jay")*(b2:b22=1)*c2:c22)
note that the ranges must be the same size and not complete columns

 

[Guest]

More specifically, I'm saying Add the numbers in the range D222 if A2:A22
="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference.

 

[Roger Govier]

Hi

Then use
=sumproduct((a2:a22="Corporate")*(b2:b22="Zone4")*d2:d22)

a2:a22 will return True or False
b2:b22 will return True or False

The Multiplication will coerce the True's to 1's and the False's to 0's,
hence
1 x 0 x 100 =0
1 x 1 x 150=150
0 x 1 x 120 =0

Sumproduct then just sums the results of these array multiplication to
give your result

 

[Guest]

That's brilliant! I never looked at it that way (True =1, False =0). Pretty
obvious but I wasn't aware you could actually coerce the numeric value!
Thank you Roger!

 

[Don Guillett]

Had you thought about what I sent you should have been able to modify it
yourself.

 

[Guest]

You know .......All it took was for someone to politely explain it to me.
I'm not used to thinking in terms of "adding" or "multiplying" TRUE and
FALSE. Thanks for your concern, Don.

 

[Don Guillett]

And all I was saying is that you could have modified this to your need and
tried it.

=sumproduct((a2:a22="Jay")*(b2:b22=1))
=sumproduct((a2:a22="corporate")*(b2:b22="zone4")*d2:d22)

More specifically, I'm saying Add the numbers in the range D222 if A2:A22
="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference.
====================================

 

[Ragdyer]

XL help files contain *nothing* about all the possibilities that this
function can perform.

Check out this link of Bob Philips for a truly enlightening explanation!<g>

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[sohail]

pleasae learing me sumproduct formula example

=sumproduct((a2:a22="Jay")*(b2:b22=1))
to sum c based on that criteria

=sumproduct((a2:a22="Jay")*(b2:b22=1)*c2:c22)
note that the ranges must be the same size and not complete columns