Consolidating multiple criteria in SUMIFs

Hi All,

I’m currently working on an excel report that sums staffing data from another report based on location.

I using a sumifs equations that adds up FTEs by matching the location in my staffing report to the location on the data report, however, there is a slight twist. Due to a few inconsistencies in location name (i.e. NY on my report versus New York on the data report) I decided to add the following matrix that allows me to match the location from my staff report to a 7 digit “location id” (i.e 138NY11) that should match exactly to data in one of the columns of data report:

A B C D E F G H I J K L
New York 138NY11 138NY61 138NY99 138NY90 141NY91 BLANK BLANK BLANK BLANK BLANK BLANK
Philadelphia 126PH80 126PH04 BLANK BLANK BLANK BLANK BLANK BLANK BLANK BLANK BLANK
Dallas 144DA01 144DA56 151DA01 151AD02 142DA21 142DA02 141DA01 141DA10 141DA40 141DA93 141DA98

Current I run a version of the sumifs equation:

Sumifs(‘Data Report’!F:F,’Data Report’!A:A,index(‘Location Matrix’!B:B,match(‘Staff Report’!A2,’Location Matrix’!A:A,0))

Each cell in my Staff Report has the sum of 10 of these sumifs equations, one for each potential location id (i.e. “location matrix’!C:C, “location matrix’!D:D,etc.)

Overall this method works but it’s very inefficient when it comes to adding tracking down a potential issues or add additional criteria.

I was wondering if there is any way to consolidate the 10 location id sumifs into one sumifs (or sumproduct or any other function)

Thanks for any and all help in advance and please let me know if you need additional info.

Originally Posted by FDibbins

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Thanks for the recommendation, attached please find a sample workbook.