Excel Query - Quarterly Results

bod1467

Consider the following layout ...

Column A has a list of dates (in format DD/MM/YYYY like 14/03/2016) and some of the dates are in 2015

Column B has monetary values in £

The dates in column A are not sequential / in date order (and I don't want to reorder/sort the columns to do so)

The question ... how can I (e.g. in cell C1) create a formula* that looks at the list of dates in column A and sums only those values in column B whose dates in column A fall within quarter 4 2015 (1st Sep to 31st Dec)?

Once I've got the idea I'll be able to convert it to get me sums for Q1/2016, Q2/2016 etc.

TIA

* I know I can manually add the relevant cells, but when the list gets long this becomes onerous - I'm looking for a smarter solution.

Discodave

Sounds like you need a filter applying to Colum C. You can then insert which Quarter each date applies to, then use filter to select Q1, 2, or 3 etc


If you want, send me over the dets and I'll knock up for you

Stompa

https://exceljet.net/formula/sum-if-date-is-between

Discodave

Stompa wrote: »

https://exceljet.net/formula/sum-if-date-is-between

not sure how that would work Stompa

cookie365

Will a pivot table do?

https://support.office.com/en-us/article/Group-or-ungroup-data-in-a-PivotTable-report-c9d1ddd0-6580-47d1-82bc-c84a5a340725?ui=en-US&rs=en-US&ad=US&fromAR=1

Stompa

Discodave wrote: »

not sure how that would work Stompa

You've lost me there, surely it answers the OP's question precisely?

bod1467

Stompa wrote: »

https://exceljet.net/formula/sum-if-date-is-between

Looks exactly like what I need. Thanks!

I thought SUMIF or SUMIFS would come into it, but I wasn't sure how best to implement it.

bod1467

cookie365 wrote: »

Will a pivot table do?

https://support.office.com/en-us/article/Group-or-ungroup-data-in-a-PivotTable-report-c9d1ddd0-6580-47d1-82bc-c84a5a340725?ui=en-US&rs=en-US&ad=US&fromAR=1

I'd thought about a pivot table, and I'm sure it could work, but I was hoping for a slightly simpler solution - what Stompa has suggested looks like it will work for me.

bod1467

Discodave wrote: »

Sounds like you need a filter applying to Colum C. You can then insert which Quarter each date applies to, then use filter to select Q1, 2, or 3 etc


If you want, send me over the dets and I'll knock up for you

I could do that, but it wouldn't really gain me anything over what I'm already doing.

PS - it's confidential financial info, so couldn't be shared anyway.

bod1467

Just to update ... Stompa's linked solution worked perfectly.