Complex Report Help

dinokathy

New Member
Joined
Nov 15, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello,

I am hoping someone on here can help me put this together. I have been working on this for a few hours and just cannot figure out how to make it work. Here's what I need help with doing.


The tabs are as follows
Imported Report - the data that will populate the COVID Report tab will get pulled from here. I want to be able to cut and paste the data on the Imported Report tab and have it calculate and update the COVID Report tab.
Market List - This breaks down the markets by Market Name and then the zip codes associated with those individual markets.
Covid Report - What I need to know here is # of drives scheduled in each market (columns G-O), how many units are booked (columns P-X).

What I want the spreadsheet to do is look at the data in Imported Report, compare the zip code (column U) against the Market List and tally up on the COVID Report tab the number of drives in said Market Name and the total units booked (from Imported Report, column AS).

I have tried VLOOKUP and MATCH and LOOKUP and just cannot get it to work the way I need it to and it is driving me nuts!

The data I want to be able to review is how many units are booked in each month in each market segment, which is made up of several zip codes.

Your help is so greatly appreciated. Thank you in advance.
 
OK. Question
Is this correct? Phew!

The number of lines on the Imported Report will vary each time it is pulled. Is there a way I can modify this formula so it takes that into account? When I change the rows it is looking at from 2 to 36 to 2 to 15000 but there's only 14,999 lines of information on the report, it returns #N/A in the result. When I manually change the row range back, the data is there. Is there a way to get it to ignore blank results?

=SUMPRODUCT(('Imported Report'!$BD$2:$BD$36='COVID Report'!$B4)*(G$1=MONTH('Imported Report'!$B$2:$B$36)))
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Reminder: the P to X column block contains ARRAY FORMULAS, so you need to resolve with CTRL+Shift+Enter. Actually, because of the SUMPRODUCT you don't need to make this an array formula. Sorry to mislead.

I didn't have a problem changing this to 1500 vs 36 or even 15000. You also would need to fill down my helper column formula.

Maybe consider making the HELPER column: =IF(ISBLANK(A2),"",INDEX('Market List'!$A$2:$A$1500,MATCH(U2,'Market List'!$B$2:$B$1500,0))) or whatever number you need.
 
Last edited:
Upvote 0
And right about here is where I learn that I have A LOT to learn about Excel! :) I will do some reading up on how to make all that come together and try to figure it out before I come back here with my hands up in the air saying 'Help me please'! All my Excel has been self taught and based on need. As my understanding of everything Excel can do for me expands, so does my knowledge and I cannot thank you enough for your help and sticking with me on this. Thank you!
 
Upvote 0
OK. I am at a total loss. I cannot get the P - X column on the COVID Report to add up the totals by month by market in column AS on the Imported Report tab. I have so much to learn when it comes to Excel. Help is sooooo appreciated. Thank you.
 
Upvote 0
See if this helps:

COVID Planning Spreadsheet - Help.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1101112123456
2# Drives Scheduled# Units Booked
3DistrictMarketActive DonorsPlanned Drives Per MonthPlanned APOPlanned Monthly CollectionsOctNovDecJanFebMarAprMayJunOctNovDecJanFebMarAprMayJun
4Atlanta Metro WestAcworth GA RSMO1,2342.02550958000000454309583000000
5Atlanta Metro WestAdairsville GA1,2342.0255011000000049670000000
6Georgia SouthAlbany GA1,2342.02550344000000160249299000000
COVID Report
Cell Formulas
RangeFormula
G1:O1G1=MONTH(DATEVALUE(G$3&" 1, 2020"))
F4:F6F4=D4*E4
G4:O6G4=SUMPRODUCT(('Imported Report'!$BD$2:$BD$15000='COVID Report'!$B4)*(G$1=MONTH('Imported Report'!$B$2:$B$15000)))
P4:X6P4=SUMPRODUCT((MONTH(DATEVALUE(G$3&" 1, 2020"))=MONTH('Imported Report'!$B$2:$B$15000))*('Imported Report'!$AS$2:$AS$15000)*((LEFT($B4,FIND(" ",$B4)-1)='Imported Report'!$S$2:$S$15000)))
 
Upvote 0
Thanks for the help again!

That didn't work on the spreadsheet I have since we made the helper column for the Market Name. I have reuploaded a new spreadsheet with that in there. Again, thank you so much. I cannot express just how much this is helping.

COVID Planning Spreadsheet - Help - NEWEST.xlsx
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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