How to sum values based on criteria in another column in Excel

[DanDBZ]

Hi,

I am wondering if there is an easy conditioning format to have a cell sum all the rows of a column that contain a certain date in the column next to it?

Dan

 

[footoo]

CF cannot return the result of a formula - it can only do formatting based upon specified criteria.

Assuming you want to sum A2:A10 if the date in A1 is 1 July 2017 put this in a blank cell :
=IF(A1=DATE(2017,7,1),SUM(A2:A10),"")

 

[FDibbins]

Use SUMIF()...
=SUMIF(date-range, date, sum-range)
date can either be hard coded or put into its own cell (preferred) and then referenced.

If you want to sum between 2 dates, change to sumifS()...
=sumifs(sum-range, date-range, ">="&start-date, date-range, "<="& end date()
again, the start date and end date are better referenced, than hard coded

 

[FDibbins]

CF cannot return the result of a formula - it can only do formatting based upon specified criteria.

Assuming you want to sum A2:A10 if the date in A1 is 1 July 2017 put this in a blank cell :
=IF(A1=DATE(2017,7,1),SUM(A2:A10),"")

Unless that is copied down to all cells, and then that range is summed, I dont see how that will work? If A1 does match, it will sum the whole range

 

[footoo]

Unless that is copied down to all cells, and then that range is summed, I dont see how that will work? If A1 does match, it will sum the whole range

I guess I didn't read his post properly

 

[DanDBZ]

Hi All,

Thanks for the replies,

I am after a formula that sum's all rows in a column (A1:A25) which ever cells in B column state July

So sum A1 if B1 is july, Add A2 if B2 is july etc.

 

[FDibbins]

Did you try my post #3 suggestion?

 

[DanDBZ]

I did try that Ford,

i couldnt get it to work.

 

[Jonmo1]

I am after a formula that sum's all rows in a column (A1:A25) which ever cells in B column state July

That's a little bit different.
Previous suggestion was for a specific single date, like say July 4th 2017.
But now you're saying any date within July? So Any date between July 1st and July 31st ??

Which version of Excel are you using?
Are the dates real excel dates, confirm with
=ISNUMBER(A1) and filled down.
Do those return True or False ?

 

[DanDBZ]

Hi Bryan,

thanks for the reply.

i can confirm that the cell with the date can have anything as i have only just started it but i want to capture the month and year.

I am using 2016 excel from 365.

I can let you know what the sheet is for and it may give you an understanding. I write invoices out to my clients and just log the invoice amount and date it was sent. My accountant wants me to show how much i am invoicing per month but due to the invoices being sent randomly (first invoice could be july, second could be aug, third could be july, it is hard for me to track automatically without filtering