Excel formula help!

Debt_Free_Chick

Doozergirl wrote: »

Essentially, I'm trying to add up all the commission made by people with a certain job title(bread) in a certain area(butter).

It is SUMIF, but you don't need the AND

What you're asking is "gimme the sum of those cells in column H which have "bread" in column G"

Tom has the right answer here

SUMIF(A1 : A10,"bread",B1:B10)


Which needs to be edited to
=SUMIF('Sheet2'!G2:G90,"bread",H2:H90)

The "AND" is implicit in the SUMIF function - as it only sums those in column H if there is (also) "Bread" in column G.

Doozergirl

Okay, I've got it! I did try to explain it but didn't do it properly, sorry. I did try

I needed the sum of a completely different column which is full of the commission where someone with the job title of 'bread' working in an area called 'butter' sold something.

Apparantly there's something called a conditional sum wizard which I don't seem to have, but I've managed it (I think, well I have a proper number in the cell now|!) with an array formula, of which I know nothing!

It's along the lines of this, so you're right, there's no AND

http://www.ozgrid.com/Excel/arrays.htm

To get the total cost of male Spaniels sold:
=SUM(IF($A$2:$A$200="Male",IF($B$2:$B$200="Spaniel",$E$2:$E$200,0),0))

It was much easier than this writing SQL queries, I'm sure. It was a while ago I'd like to get another condition in there, but maybe I'm pushing my luck!

Thanks, guys. Sorry for being rubbish. I'm going to add it up by hand and see if it works!

tomstickland

This works !:

}=SUM(IF((A1 : A10="bread")*(B10:B10="butter"),C1:C10)) }

This also works

}=SUM((A1 : A10="bread")*(B1:B10="butter")*(C1:C10)) }

The MSE forum won't show the open bracket properly, so I've put a closed bracket at the start.

You have to do Ctrl-Shift-Enter or put the swirly brackets in to make it an array formula.

So if the column A value is "bread" and the column B value is "butter" then the logical multiplication comes out as true and it includes the element in column C.

I've tested it and it does what it should.

Doozergirl

:wall: :wall: :wall:


Going to try yours Tom, mine isn't quite right!

Doozergirl

LOL!

Okay, yours works! In fact, mine works but somebody covered for someone else's patch and the name was out of place

The thank you button has disappeared, but thank you!!!

Fairwinds

If you don't fancy the array formula that Tom gave you could also use

=SUMPRODUCT(--(A1:A10="bread"),--(B1:B10="butter"),C1:C10)

Sumproduct is more useful when there is more than one condition to satisfy

Hope it helps

Fairwinds

That should be A1(colon)A10

Debt_Free_Chick

GordonD wrote: »

If you don't fancy the array formula that Tom gave you could also use

=SUMPRODUCT(--(A1:A10="bread"),--(B1:B10="butter"),C1:C10)

Although my experiences with Excel tend to lead to little demons rather than little angels :rotfl: :rotfl:

Yes, I know .... the bloomin post editor converted what you typed! And has me thinking of little demons again! :rotfl:

Doozergirl

I'm emotionally exhausted!

tomstickland

Don't forget your colon.