SumIf Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Jay

look at SUMPRODUCT

Look at the Help and search the Archives for examples

Regards

Trevor
 
=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
 
More specifically, I'm saying Add the numbers in the range D2:D22 if A2:A22
="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference.
 
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
 
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!
 
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.
 
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)
Click to expand...

More specifically, I'm saying Add the numbers in the range D2:D22 if A2:A22
="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference.
====================================
 
pleasae learing me sumproduct formula example

Don Guillett said:
=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
Click to expand...
 
Back
Top