How to pull data for quarterly and annually information?

ishpahuja

New Member
Joined
May 6, 2015
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Hi friends

I am struggling to build a dashboard which shows quarterly and annual results side by side.
I have my detailed sales information in another sheet like this:
Column A Column B to Column AZ
# of Referrals Month wise data (i.e. the # of referrals received in each month starting May 2019 until Jul 23)
# of converted clients Same format as above
Cumulative clients Sum of total clients till a particular month in that column

I have the modeled the top 4 rows (column B onward) as follows:
Month no. (1,2,3,4...... May 2019 being 1)
Month (01-May-2019, 01-Jun-2019 and so on)
Quarter (Q3F19 for May 2019, Q3F19 for Jun 2019 - Text manually entered)
Financial Year (2019 until Oct 2019, 2020 for next 12 months and so on)

Now, on the dashboard, I am trying to get the quarterly and annual information rolled up but I can't seem to have come up with a single formula in cell which I can drag right. This is what I am looking for?

Column B onwards (Q3F19, Q4 F19, FY2019, Q1F20....FY2020, FY2021, FY2022, FY2023)
# of Referrals
# of converted clients
Cumulative clients

Please guide how can I do that? I can share the model if that is more convenient.
Thanks in anticipation.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top