Help on Excel Formula using Sum and If

[bemcbride]

My spreadsheet has a list of customers and invoice amounts and a yes or no that indicates whether or not they have paid. I want to sum only the amounts that have been marked as a yes (for paid.)

I tried using the sumif function but it didn't work correctly.
My next idea was to do an imbedded if statement however when i got to the part of to sum it I got stuck.

Some help would be greatly appreciated!

 

[Smitty]

Welcome to the Board!

If you have Excel 2007+ you can use SUMIFS:

Excel 2010
	A	B	C	D	E	F
1	Customer	Invoice Amount	Paid?		Company	Total Paid
2	ABC Imports	$47.54	Yes		ABC Imports	$227.89
3	XYZ Co	$79.53	No		XYZ Co	$43.55
4	DEF Exports	$26.91	Yes		DEF Exports	$156.91
5	ABC Imports	$92.77	Yes
6	XYZ Co	$46.11	No
7	DEF Exports	$85.76	Yes
8	ABC Imports	$87.57	Yes
9	XYZ Co	$5.65	No
10	DEF Exports	$44.24	Yes
11	ABC Imports	$45.30	No
12	XYZ Co	$43.55	Yes
13	DEF Exports	$67.30	No
Sheet1

Cell Formulas
Range	Formula
F2	=SUMIFS($B$2:$B$13,$A$2:$A$13,E2,$C$2:$C$13,"Yes")
F3	=SUMIFS($B$2:$B$13,$A$2:$A$13,E3,$C$2:$C$13,"Yes")
F4	=SUMIFS($B$2:$B$13,$A$2:$A$13,E4,$C$2:$C$13,"Yes")

HTH,

 

[Marcelo Branco]

Hi,

Could you post a data sample?

M.

Edit

Smitty has already provided a solution

 

[VoG]

Try something like

=SUMPRODUCT(--(A1:A100="Shell"),--(B1:B100="yes"),C1:C100)

 

[Jeff Meyers]

SUMIF should work for you in this instance.

Here's an example for your situation:

Excel Workbook
	A	B	C
1	Customer	Inv Amt	Paid?
2	A	100	Y
3	B	200	N
4	C	300	N
5	D	400	Y
6	E	500	Y
7	F	150	Y
8	G	250	N
9	H	350	Y
10	I	450	N
11	J	550	Y
12	K	975	N
13	L	875	N
14	M	775	Y
15	N	675	N
16	O	575	Y
17
18	Total Y Paid	3,400
19
20
21	----------------	------------	----------
22
Sheet4

Excel 2010

Cell Formulas
Range	Formula
B18	=SUMIF(C2:C16,"=Y",B2:B16)

 

[bemcbride]

Well that was a definite blonde moment lol