2 sumif functions in one formula

T

Todd

Is it possible to have 2 sumif functions in one formula
and return the same results. If so, how would the formula
be written? Thanks.

Todd
 
D

Don Guillett

If you are to return the same results why do you need 2 formulas?
It would be good to give specifics with examples.
 
T

todd

The deal is that I want it to look at a date in column A
and a specific word in column C and if both match what I
have on another sheet I want it to sum what is in column C.
 
D

Don Guillett

Still not getting all of this but this might be of help.
As the prison guard said in Cool Hand Luke "What we have here isa
fauluretocommunicat"
Yes, I do know how to spell.

where the date desired is in cell b1on sheet2
=sumproduct((rngA=sheet2!b1)*(rngC=sheet2!b2)*rngD)
 
M

Mokey

Hello, Don. I work with Todd. I see that the formula
works, but how does it work? I can't follow the steps
that the formula is taking. Help?
 
D

Dave Peterson

If you want more info:
Chip Pearson has notes for array formulas at:
http://www.cpearson.com/excel/array.htm
(and =sumproduct() is another way of entering an array formula)

And you may want to get a copy of Bob Umlas's white paper:
http://www.emailoffice.com/excel/arrays-bobumlas.html


The formula is essentially doing:
for each cell in RngA return true/false (if it matched sheet2!b1)
For each cell in rngC return true/false (if it matched)

If the ranges are small, you can highlight that portion of the formula and hit
F9 to see how it evaluates:

Highlight "rnga=sheet2!b1" and hit F9 and you'll see a bunch of true/falses.

Now the product part of sumproduct says to multiply those true/falses against
the corresponding true/false.
(true*true evaluates to 1, anything else T*F, F*T, F*F evaluates to 0.)

Then you have a sequence of 1/0's for those two portions of the formula.

When you multiply the 1/0's by the values in rngD, you'll get the value in each
cell in rngD if it was multiplied by a 1 or 0 if it was multiplied by the 0.

Then the sum portion of sumproduct adds up all those individual pieces:
 
Top