finding year instead of date

[Ronderbecke]

I realize this is probably something small that I am overlooking, but currently I have a way to get a total of a certain month and I need to change it to a certain year. Is it possible to change something in this equation so it pulls everything for the year instead of just the month?

=SUMPRODUCT(IF('Travis Jones'!B2:B-DAY('Travis Jones'!B2:B)+1=DATE(2017,12,1),'Travis Jones'!D2:D))

 

[steve the fish]

Try:

=SUMPRODUCT(--(YEAR(B2:B100)=2017),D2:D100)

or maybe:

=SUMIFS(D:D,B:B,">="&DATE(2017,1,1),B:B,"<"&DATE(2018,1,1))

Add the sheet names to the appropriate ranges.

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G
1	Date		Amount	Year	2016	20
2	1-Dec-16		20	Year	2017	170
3	1-Dec-17		40
4	20-May-17		30
5	12-Dec-17		100
6						140
7
Travis Jones

Cell Formulas
Range	Formula
G1	=SUMPRODUCT(--(YEAR('Travis Jones'!B2:B1000)=F1),('Travis Jones'!D2:D1000))
G2	=SUMPRODUCT(--(YEAR('Travis Jones'!B2:B1000)=F2),('Travis Jones'!D2:D1000))
G6	=SUMPRODUCT(--('Travis Jones'!B2:B1000-DAY('Travis Jones'!B2:B1000)+1=DATE(2017,12,1)),('Travis Jones'!D2:D1000))

Edit the 1000 to an appropriate number considering your data.

 

[Ronderbecke]

For some reason these are not working but I think it may be because of the date format in column b?

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Customer	CRC Date	System Size	PPW Payout	Panel Upgrade
Douglas Rogers	7/11/2017	5.61	$4.17	YES
Andrew Bean	7/10/2017	13.63	$3.91

<colgroup><col style="width: 172px"><col width="72"><col width="79"><col width="81"><col width="94"></colgroup><tbody>
</tbody>

Is the structure of the date messing up the formula by chance? All of these formula's keep returning 0?

 

[Ronderbecke]

Try:

=SUMPRODUCT(--(YEAR(B2:B100)=2017),D2:D100)

or maybe:

=SUMIFS(D:D,B:B,">="&DATE(2017,1,1),B:B,"<"&DATE(2018,1,1))

Add the sheet names to the appropriate ranges.

I must have fat fingered something because I was going through trying again and again and the first formula worked. Thanks so much.

 

[Ronderbecke]

With the year working, is there a way to break down a year into quarters? I have tried converting the 2017 to each quarter for data referencing but everything I try doesn't seem to work? The formula works perfect for year, but is there a way to split that first formula into Q1, Q2, etc?

 

[Dave Patton]

--

 

[steve the fish]

This would do first quarter of 2017:

=SUMPRODUCT(--(YEAR(B2:B100)=2017),--(ROUNDUP(MONTH(B2:B100)/3,0)=1),D2:D100)

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G	H
1	Date		Amount		2016	20
2	1-Dec-16		20
3	1-Dec-17		40
4	20-May-17		30		1-Jan-19	203	Qtr 1
5	12-Dec-17		100		1-Apr-19	207	Qtr 2
6	5-Jan-19		101		1-Jul-19
7	20-Jan-19		102
8	4-Apr-19		103
9	5-May-19		104
Travis Jones

Cell Formulas
Range	Formula
G1	=SUMPRODUCT(--(YEAR('Travis Jones'!B2:B1000)=F1),('Travis Jones'!D2:D1000))
G4	=SUMIFS(D:D,B:B,">="&F4,B:B,"<"&F5)
G5	=SUMIFS(D:D,B:B,">="&F5,B:B,"<"&F6)