Sumif question

[bionicle]

Hi All,

have a question for you, if I have a list of dates in the format of 05/01/2016 but want to return a sum value against all the dates listed as January how do I do it?

 

[pocquet]

HI,

Do you mean a count (eg the number of dates in jan)? if so you need to use countif

 

[Fishboy]

Hi All,

have a question for you, if I have a list of dates in the format of 05/01/2016 but want to return a sum value against all the dates listed as January how do I do it?

Hi bionicle,

Assuming that your dates and in column A and the values are in column B you could try this:

=SUMIFS(B1:B100,A1:A100,">="&DATE(2016,1,1),A1:A100,"<="&DATE(2016,1,31))

Alternatively, assuming your dates are in row 1 going across and your values are in row 2 then you could try this:

=SUMIFS(A2:CV2,A1:CV1,">="&DATE(2016,1,1),A1:CV1,"<="&DATE(2016,1,31))

 

[Aladin Akyurek]

Hi All,

have a question for you, if I have a list of dates in the format of 05/01/2016 but want to return a sum value against all the dates listed as January how do I do it?

=SUMIFS(B:B,A:A,">="&(1&E1),A:A,"<="&EOMONTH(1&E1,0))

where column B is the reference to sum, column A houses dates, and E1 the month name january.

 

[steve the fish]

Something like this:

=SUMIFS(A:A,B:B,">="&"01/01/2016",B:B,"<"&"01/02/2016")

The dates can be replaced for cell references if you want.

 

[bionicle]

Hi bionicle,

Assuming that your dates and in column A and the values are in column B you could try this:

=SUMIFS(B1:B100,A1:A100,">="&DATE(2016,1,1),A1:A100,"<="&DATE(2016,1,31))

Alternatively, assuming your dates are in row 1 going across and your values are in row 2 then you could try this:

=SUMIFS(A2:CV2,A1:CV1,">="&DATE(2016,1,1),A1:CV1,"<="&DATE(2016,1,31))

works a treat, thank you

 

[bionicle]

Thanks all, most appreciated.

 

[Fishboy]

works a treat, thank you

Happy to help

 

[bionicle]

Just noticed something, on my spread sheet I have multiple years; what if I only want to look at the month rather than specific years i.e all dates say within January rather than just 2016?

also how would I get it to look at a third column (Q) so it only summed against the word WON

 

[Fishboy]

Just noticed something, on my spread sheet I have multiple years; what if I only want to look at the month rather than specific years i.e all dates say within January rather than just 2016?

also how would I get it to look at a third column (Q) so it only summed against the word WON

You'd probably want to change this to a SUMPRODUCT as follows:

=SUMPRODUCT((MONTH(A1:A100)=1)*((Q1:Q100)="WON")*B1:B100)

The above checks that the month in column A is 1 regardless of the year, it looks at column Q for the value "WON", then sums the values from column B of the corresponding rows.