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.
 

dinokathy

New Member
Joined
Nov 15, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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)))
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,585
Office Version
  1. 365
  2. 2010
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:

dinokathy

New Member
Joined
Nov 15, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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!
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,585
Office Version
  1. 365
  2. 2010
Don't hesitate to ask for help. Everyone here wants to help others.
 

dinokathy

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

ADVERTISEMENT

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.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,585
Office Version
  1. 365
  2. 2010
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)))
 

dinokathy

New Member
Joined
Nov 15, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,585
Office Version
  1. 365
  2. 2010
Made some adjustments in your NEWEST. Check it.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,662
Messages
5,626,160
Members
416,165
Latest member
baobao3

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
Top