Apr 13 2020 10:41 AM
I am trying to add multiple values based on zip code. For example, I have a data set based on zip code and want to select multiple zip codes then sum the data to the immediate right of those multiple zip codes.
Example:
A | B | |
1 | 55001 | 100 |
2 | 55002 | 200 |
In this scenario I want to include both 85001 and 85002 and return an output of 300.
This is probably pretty simple but I cannot figure it out. Thanks in advance for your help.
Apr 13 2020 11:24 AM
You start this out by referring to VLOOKUP. Although that could work, it's probably not the most efficient. From your description it sounds as if you would benefit from looking into the SUMIFS function instead. But some of the answer might also depend on how many different zip codes you might be expecting to use--at least I'd want to know what's the maximum.
And depending on what your answer is to that, I'd start to wonder if there isn't something in common (some other attribute) that identifies the zip codes that you're wanting to summarize this way and whether or not that attribute could be added to the table, to make it easier to use SUMIFS or even SUMIF.
Anyway, there are lots of resources, in addition to Excel's own Help text, that you can refer to for those functions. Since you already know VLOOKUP, I'm assuming you won't mind doing your own research into what fits best. By all means, come back here with any follow-up questions.
Apr 13 2020 01:56 PM
The largest group will have 75 zip codes. I am trying multiple criteria in SUMIFS but for some reason the response is 0 rather than 300. Definitely user error.
Apr 13 2020 03:37 PM
The second paragraph in my last message contained an implicit question: "....depending on what your answer is to that, I'd start to wonder if there isn't something in common (some other attribute) that identifies the zip codes that you're wanting to summarize this way and whether or not that attribute could be added to the table, to make it easier to use SUMIFS or even SUMIF," and now that you're acknowledging that there can be as many as 75 zip codes in any one grouping, I now really wonder what lies behind the selection of zip codes in the first place.
That aside, I think you're looking for some of the newer Dynamic Array functions. I'm just learning them myself. Here's a resource you might find helpful...https://exceljet.net/search?query=dynamic+arrays
Apr 14 2020 12:28 AM
If SUMIFS() returns zero instead of expected value, wrong criteria is only one of the reasons. Another one if we have texts which represent numbers instead of numbers.