YTD SUMIF excluding SOME blanks & zeros

[kelstar]

Hi all, I am trying to do a SUMIF so that YTD for last year matches this year.
So in this example I want the SUM of Jan/Feb 2018 vs SUM of Jan/Feb 2017 to show in my totals at the bottom.
I have a formula that works except it falls apart when I have a zero for a month that should be included (like Jan in this example)
This is what I have
=SUMIF(C14:C25,B14:B25,"<>"&""&0)
So total for 2017 is showing as 1 when it should be 7.
I've tried everything I can think of to fix it! Any help would be appreciated!
Apologies work browser settings won't allow me to copy / paste properly.
The figures for 2018 are B14:B25 and 6 for 2017 is C14:C25 if that helps.

[TABLE="width: 233"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]BOOKINGS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2018[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]JAN[/TD]
[TD]0[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]FEB[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]MAR[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]APR[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]JUN[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]JUL[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]AUG[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]SEP[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]OCT[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]NOV[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]DEC[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD +/-[/TD]
[TD="colspan: 2"][/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

=SUMIF(C14:C25,"<>0",B14:B15)

is the correct syntax for a hard coded condition


Forget what I said before, you want YTD.

 

[sheetspread]

This does it:

Excel 2010
	A	B	C
1	BOOKINGS
2		2018	2017
3	JAN	0	6
4	FEB	4	1
5	MAR	0	1
6	APR	0	0
7	MAY	0	3
8	JUN	0	1
9	JUL	0	0
10	AUG	0	2
11	SEP	0	0
12	OCT	0	1
13	NOV	0	3
14	DEC	0	3
15	TOTAL	4	1
16			7
Sheet6

Cell Formulas
Range	Formula
C16	=SUMPRODUCT(--(MONTH($A$3:$A$14&1)<=2)*$C$3:$C$14)

where 2 is the month number

 

[jtakw]

Hi,

Assuming your Column A Months are Text and Not Real Date values, And you want a running YTD as each month passes, modification to Sheetspread's formula:

Book1
	A	B	C
12	BOOKINGS
13		2018	2017
14	JAN	0	6
15	FEB	4	1
16	MAR	0	1
17	APR	0	0
18	MAY	0	3
19	JUN	0	1
20	JUL	0	0
21	AUG	0	2
22	SEP	0	0
23	OCT	0	1
24	NOV	0	3
25	DEC	0	3
26	TOTAL	4	7
Sheet45

Cell Formulas
Range	Formula
B26	=SUMPRODUCT((MONTH($A14:$A25&1)TODAY()))*B14:B25)

B26 formula copied to C26.