sumproduct not working when value converted as number

[kinkart]

Hello,

I have a spreadsheet with the following formula in O13:

=SUMPRODUCT((D13=MASTER[ name])*(H13=MASTER[[#Headers];[1]:[20]]);MASTER[[1]:[20]])

In H13, the "value" is based on the list from 1 to 20 that is
=MASTER[[1]:[20]

I have the little green triangle in the top left corner of that cell that tells me that says:
"Number stored as text".

When I convert this to number, my sumproduct formula doesn't work anymore.

I'd like to set up a data bar in that H13 cell, but it won't let me until it's a number.
Any ideas on what's wrong? Can we either fixed the sumproduct formula so that it works when H13 is a number, or set up a data bar that can use text values?

Thanks a ton!

 

[NARAYANK991]

Hi ,

Can you try this ?

=SUMPRODUCT((D13=MASTER[[ name]])*(H13=VALUE(MASTER[[#Headers],[1]:[20]])),MASTER[[1]:[20]])

Narayan

 

[kinkart]

This partially solve the issue.
After putting the VALUE you suggested, I can convert the number in H13 as number, and both SUMPRODUCT formula and databar works.
However, seems that if I pick another value in the list, I have to "convert to number" the cell every time or it won't work. This is not practical, since I have that worksheet protected and so my users won't be able to do so.
Any way to have that list display numbers all the time, without getting it formatted as text?

 

[Faseeh]

Hi Kinkart,

Plz uploading a sample file!

 

[kinkart]

attached is a sample.

What I'd like in the end is to create a data bar in H13 (and the rest of similar cell) so that when value selected is 0, it's empty, and when it's 20, it's full.

 

[Faseeh]

Hi Kinkart

Please see this file, I have made a new named range LevelNum and now it is working.

 

[kinkart]

great! thank you!