Excel Vlookup or SUMIFS with different name from Data out of SFDC

[neeckman]

Hi All -

I am working on a putting together a sales report using data from reports created in SFDC. The issue im running into is on my Summary tab I want the names to be different then how they are listed in SFDC.
For example: I want to have it shown as ABC Corp but the data in SFDC lists ABC Corp, Inc. I have to do this for ~40 accounts.

I plan to use SUMIFS but I only know how to do this when the names exactly match in the summary tab and the raw data.

Thanks for the help!

 

[KillerOfGiants]

If I recall correctly, one cannot use SUMIFS with only partial cell data, which is where you run into an issue. In this case, I fall back to the old SUMPRODUCT function, as it allows partial cell data to be accounted for. For example:

=IF($B$1="","",SUMPRODUCT(--(LEFT($H$2:$H$10,LEN($B$1))=$B$1),($I$2:$I$10)))

Where cell $B$1 is what you are looking up (from the Summary tab), column $H$2:$H$10 is how it would be portrayed in your SFDC example, with column $I$2:$I$10 being the summed data.
What this formula does is first gives no result (or whatever result you have) if your Summary Data cell is empty. Then it looks at the LEFT portion of your SFDC list equal to the length of the data from your Summary tab (so long as that matches you should be fine, it can't account for misspellings or typos such as ACB Corp).
The breakdown of the formula itself: "--(LEFT($H$2:$H$10,LEN($B$1))" this portion of the formula results in a TRUE or FALSE result, with the -- effectively changing it to 1 or 0.
"($I$2:$I$10)" is the actual column to be summed up for you.

SUMPRODUCT can actually have more criteria added, just separated by a comma and with the -- in front of the parens. It creates a "table" of multiplication and then sums it. So, say you have a data set that has two different criteria in columns A and B, with the calculated value in column C, the virtual table could luck like this:

1 0 20
1 1 32
0 0 45
0 1 66
1 1 12
0 1 18
1 1 23

Only rows 2, 5, and 7 match both criteria, and it would then sum 32, 12, and 23 giving a final value of 67