PDA

View Full Version : Solved: SUMIF Formula



RobertBC
09-19-2006, 11:04 AM
good day!

I had this problem of getting the total of for a sales..
I want to sum all the values on a single column with a condition,
and that condition is refer to another column or value of a cell in a column..

examples..
sum if <Range of Cells> equal to "cash"
sum if <Range of Cells> equal to "check"
sum if <Range of Cells> equal to "card"

sum if <Range of Cells> not equal to "cash"
sum if <Range of Cells> not equal to "check"
sum if <Range of Cells> not equal to "card"

attached is my example file..


thanks :bow:

mdmackillop
09-19-2006, 11:33 AM
Hi Robert
You could use SumProduct
=SUMPRODUCT(--(A$2:A$8="cash"),(B$2:B$8)) see http://www.xldynamic.com/source/xld.SUMPRODUCT.html
or SUMIF
=SUMIF(A$2:A$8,"card",B$2:B$8)

Shazam
09-19-2006, 01:09 PM
See if this helps.


Select A10

Go to:
Insert > Define > Name

Name: Array

type this into the:

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$A10," ",""",""")&"""}")

Click Ok

Then input formula in cell B10 and copied down.


=SUMPRODUCT(SUMIF($A$2:$A$8,Array,$B$2:$B$8))


Hope it works for you.

Bob Phillips
09-19-2006, 03:19 PM
Be aware that versions of Excel prior to 2002/XP will crash and burn if copying and pasting cells between worksheets where the cells contain references to defined names that refer to XLM functions.

RobertBC
09-20-2006, 06:20 AM
Hi Robert
You could use SumProduct
=SUMPRODUCT(--(A$2:A$8="cash"),(B$2:B$8)) see http://www.xldynamic.com/source/xld.SUMPRODUCT.html
or SUMIF
=SUMIF(A$2:A$8,"card",B$2:B$8)

Thank you guys i really appretiate it.. you help a lot....
Now im exploring the formula for more use....

thanks:beerchug: