Sumifs returns 0 value for alphanumeric sumrange

[questforexcel]

Hello All,

So my data from where i want to pull the sum range contains alphanumeric values in like "GS08, GS09, GS10"

The criteria1 range is the range for unique client reference nos. in cells A2:A2688. Criteria1 is to pull each inidividual client reference no. for eg. 64852345.

Criteria Range 2 is the range for all months in cells B2:B2688 i.e. January to September. Criteria 2 is to pull "September"

When I click enter it returns a value of "0". I assume this has something to do with the alphanumeric value of the sumrange for eg. GS08.

When I click on Fx function to drill down on the formula, it reads all the individual items correctly but on the displaying the final result it shows a value of 0.

I tried putting "ABS" infront of the formula, adding 0 to the sumrange but it would not accept it for the SUMIFS function.

I've really had a difficult time cracking this one. Searched online for help but couldn't find any solutions.

Could you please help my formula to return the alphanumeric value based on the 2 unique conditions. Would really appreciate all your assistance.

Thank you!

Have a good one

 

[PaddyD]

Are you saying:

1) You're trying to add up a range of alpha numerics like GS08, GS09 etc? 'Cos if so sumif() isn't going to do it, on the basis that GS08, GS09 etc aren't numbers. Options would be to count these entries instead or, if it's the 08, 09 bits you're after, separate the number portion of the strings and add them up
2) You're trying to add up numbers that are in a series that also contains alphanumeric strings (e.g. GS08, GS09, 2, 9, DS10, 11) and you want to add up the 2,9 and 11
3) Some mix of the both

...more info please

 

[questforexcel]

Hi Paddy,


Yes, I was trying to add a sum range of alphanumeric numbers. There would be a unique value to each combination of client ref no and specific month. Unique values like ‘GS06/GS07/GS08’

I would need the ‘GS’ series ahead of the numbers so cannot omit them. I thought of removing the left 2 or only include the right 2 but I would need to display the alphanumeric characters.

What would be the best way to search unique alphanumeric values based on two individual search criteria’s from a range of cells as mentioned above.

Thanks for the help!

Are you saying:

1) You're trying to add up a range of alpha numerics like GS08, GS09 etc? 'Cos if so sumif() isn't going to do it, on the basis that GS08, GS09 etc aren't numbers. Options would be to count these entries instead or, if it's the 08, 09 bits you're after, separate the number portion of the strings and add them up
2) You're trying to add up numbers that are in a series that also contains alphanumeric strings (e.g. GS08, GS09, 2, 9, DS10, 11) and you want to add up the 2,9 and 11
3) Some mix of the both

...more info please

 

[PaddyD]

Can you provide a detailed, worked example of the calculation you are trying to perform and the expected results - don't worry about the excel details, just set out the logic

 

[questforexcel]

Hi Paddy,

Thank you for your help. Below is an example:

=SUMIFS('Data - Sept'!$L$2:$L$2688,'Data - Sept'!$A$2:$A$2688,Main!$B4,'Data - Sept'!$U$2:$U$2688,"September")

Data - Sept'!$L$2:$L$2688 - The alphanumeric codes - GS09, GS08, GS07...etc
Data - Sept'!$A$2:$A$2688 - The unique client reference numbers - 45697863, 123645, 6985698...etc
Main!$B4 - Reference to the 1st unique client reference number - 45697863
'Data - Sept'!$U$2:$U$2688, - Individual month names - January to September
"September" I have september in quotes as I would like my formula to include all September figures.

So the intention of my calculation using the above formula is to arrive at the unique value for a client's alphanumeric code i.e. GS09, GS08, GS07 based on their clienct reference code for eg. 45697863 , for September the code is GS07. I'd like my formula to display GS07. Instead it currently displays "0"

Could you please help me fix or use a formula which enables to pull alphanumeric numbers based on 2 criterias.

Thank you

Can you provide a detailed, worked example of the calculation you are trying to perform and the expected results - don't worry about the excel details, just set out the logic