Consolidating a Large Amount of Data

Hello!

I have a question about how to consolidate data from 200 work sheets involving fleet management. Each work sheet has the same layout, but it has changing information. For instance, when the location of the vehicle changes, it should change the information in the corresponding cells on the master sheet.

To give some insight:

We have leased, vacant, in-shop, and out of service tabs in a row. Then we have 8 different company locations as the columns. I need a formula that can add up the total of each line item in the row in reference to the right location.

I knew an IFAND formula would be able to apply two values (0 if false and 1 if true) then I could add that IFAND formula to the others to get a total, but that means manually writing an IFAND formula for every page which there is 200~ vehicles.

My IFAND formula looks like =IF(AND('22BX5G'!F1="PINELLAS/CLEARWATER",'22BX5G'!D1="LEASED"), 0, 1). So IF F1 from that sheet is equal to that string AND D1 is equal to that string, the answer is 1. I can then add those increments to get a total. Again, that is cumbersome. This also breaks down when you try to apply multiple sheets to the formula in a range as opposed to doing each individual sheet on its own since you can't increment and get a total.

Is is possible with the method above, but it also requires formatting 200 IFAND statements, each added to the other, for every single column/row item. Is it possible to do it in a quicker way? Can this be accomplished with loops?

Best regards, and I appreciate the help anyone can give me

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.