User Defined Function help

[largeselection]

Hello,

I have a user defined function which converts a zipcode into the state it comes from written as below:

Function State(number as string)
Select case number
 
Case is = "11111"
State = "Wyoming"
Case is = "22222"
State = "New York"
Case is = "33333"
State = "California"
etc.

what I'm wondering is if it would be possible to create a user defined function which would use this function to evaluate a sum without having to physically add another column to the data.

So if I have data that is just 2 columns
Zipcode, Population

I'd like to be able to put in a custom function like Customsum(Zipcode column, State, New York, Population column)
And have it spit out what the sum of the population column is for all zipcodes within New York.

I know I could add a third column to the data which is just "STATE" and use my original UDF to just populate it, then do a sumif, but I'm wondering if it's possible to skip that step.

I've tried with some arrays but they don't work. So for example I tried {=sum(if(state(A1:A100)="NEW YORK",B1:B100,""))} hoping that that might work, but no luck.

So the UDF format I'd be looking for is something similar to Customsum(Reference range, Custom UDF to evaluate, Value from UDF to sum, Reference range to sum)

Is this possible?

 

[shg]

I'm not sure what the UDF is contributing.

How about just making a three-column table with zip, state, and population, and then using AutoFilter, Advanced Filter, a pivot table, or some formulas to extract whatever you want?

 

[largeselection]

Hi thanks for taking a look.

The reason why I'm using a UDF is because I actually have more fields than just State, there are about 30 of them. So if I have the set of data and UDFs I can create a summary by each field in a table above the data. As of now I just have 30 additional columns on the data which have the UDFs and then just sumifs in a table above, but I thought I could save some space if it would be possible to do the Array formulas using my UDFs.