Formula error or usage error?

[Capsaicin Burn]

I'm trying to total some columns. However, I only want the column totals of the rows where columns B & C are not empty. I want the totals NOT to include any rows where columns B & C are empty. I hope I'm asking this question correctly. Any help would be greatly appreciated.



Excel 2010

	A	B	C	D	E	F	G	H
1	#	Length	Height	Sq Ft	Avg Thickness	CF	Perch	Yards
2	1	35	7	245	2.375	581.875	23.275	21.55093
3	2	40	6	240	2.125	510	20.4	18.88889
4	3	10	4	40	1.625	65	2.6	2.407407
5	4	3	4	12	1.625	19.5	0.78	0.722222
6	5	2	4	8	1.625	13	0.52	0.481481
7	6	4	4	16	1.625	26	1.04	0.962963
8	7	5	5	25	1.875	46.875	1.875	1.736111
9	8			0	0.625	0	0	0
10	9			0	0.625	0	0	0
11	10			0	0.625	0	0	0
12	Using formula below			598.875	1275.125	1312.74	97.24	46.75
13	< =SUMIF(B4:C13,"<>",D4:D13) >
14	Minus the first and last characters
15
16	Summing rows 2 - 11			586	12.875	1262.25	50.49	46.75
17
18	I only want the column totals of the rows where columns B & C are not empty

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula A1 # B1 Length C1 Height D1 Sq Ft E1 Avg Thickness F1 CF G1 Perch H1 Yards A2 1 B2 35 C2 7 D2 245 E2 2.375 F2 581.875 G2 23.275 H2 21.5509259259259 A3 2 B3 40 C3 6 D3 240 E3 2.125 F3 510 G3 20.4 H3 18.8888888888889 A4 3 B4 10 C4 4 D4 40 E4 1.625 F4 65 G4 2.6 H4 2.40740740740741 A5 4 B5 3 C5 4 D5 12 E5 1.625 F5 19.5 G5 0.78 H5 0.722222222222222 A6 5 B6 2 C6 4 D6 8 E6 1.625 F6 13 G6 0.52 H6 0.481481481481481 A7 6 B7 4 C7 4 D7 16 E7 1.625 F7 26 G7 1.04 H7 0.962962962962963 A8 7 B8 5 C8 5 D8 25 E8 1.875 F8 46.875 G8 1.875 H8 1.73611111111111 A9 8 B9 C9 D9 0 E9 0.625 F9 0 G9 0 H9 0 A10 9 B10 C10 D10 0 E10 0.625 F10 0 G10 0 H10 0 A11 10 B11 C11 D11 0 E11 0.625 F11 0 G11 0 H11 0 D12 598.875 E12 1275.125 F12 1312.74 G12 97.24 H12 46.75 D13 E13 F13 G13 H13 E14 F14 G14 H14 B15 C15 D15 E15 F15 G15 H15 D16 586 E16 12.875 F16 1262.25 G16 50.49 H16 46.75 B17 C17 D17 E17 F17 G17 H17 A12 Using formula below A13 < =SUMIF(B4:C13,"<>",D4:D13) > A14 Minus the first and last characters A15 A16 Summing rows 2 - 11 A17 A18 I only want the column totals of the rows where columns B & C are not empty <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[sheetspread]

Does changing "<>" to "*" help? Are you sure the cells are empty?

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H
1	#	Length	Height	Sq Ft	Avg Thickness	CF	Perch	Yards
2	1	35	7	245	2.375	581.875	23.275	21.5509
3	2	40	6	240	2.125	510	20.4	18.8889
4	3	10	4	40	1.625	65	2.6	2.40741
5	4	3	4	12	1.625	19.5	0.78	0.72222
6	5	2	4	8	1.625	13	0.52	0.48148
7	6	4	4	16	1.625	26	1.04	0.96296
8	7	5	5	25	1.875	46.875	1.875	1.73611
9	8			0	0.625	0	0	0
10	9			0	0.625	0	0	0
11	10			0	0.625	0	0	0
12				586	12.875	1262.25	50.49	46.75

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet17​

Worksheet Formulas Cell Formula D12 =SUMIFS(D2:D11,$B$2:$B$11,"<>",$C$2:$C$11,"<>") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put the formula in D12 and drag to the right. I'd guess E12 doesn't need the formula.

 

[Capsaicin Burn]

Does changing "<>" to "*" help? Are you sure the cells are empty?

The cells are empty and the * didn't seem to work. Thanks for your response though!

 

[Capsaicin Burn]

Thanks very much Eric! This worked perfectly. Maybe one day I will understand the difference. I really do appreciate your formula and your help!

 

[sheetspread]

Ok then Eric W's sumifs is the way to go (SUMIF is usually single criterion)

 

[Eric W]

I'm a little surprised that your formula didn't cause an error. The ranges in the "IF" functions all have to be the same size. Since your condition range was 2 columns, we needed to split it out into 2 columns, which required the SUMIFS.

In any case, I'm glad we could help.

 

[Capsaicin Burn]

Ok then Eric W's sumifs is the way to go (SUMIF is usually single criterion)

Could it be because columns B & C were separated in the formula? I enjoy learning something new.

 

[sheetspread]

Yes, and I was going to say that right after reading your question (no really) but wanted to be sure SUMIF couldn't search a multi column range at all, even for just nonblanks. Sumproduct and {sum(if())} also work, especially if you have pre-2007 Excel versions that lack SUMIFs.