Solved: SUMIF Formula

[RobertBC]

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

[mdmackillop]

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]

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]

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]

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