2 sumif functions in one formula

  • Thread starter Thread starter Todd
  • Start date Start date
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
 
If you are to return the same results why do you need 2 formulas?
It would be good to give specifics with examples.
 
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.
 
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)
 
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?
 
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:
 
Back
Top