SumProduct with Conditions To Make A Table Failing

[rdelcarlo]

Hello

I have a pretty solid data set that I need to translate into a table. For some reason, however, making a table using sum product isn't working. I did an example elsewhere in the sheet that proved there's nothing wrong with my formula. So that means there's something wrong with the data?

Feel free to follow the dropbox link below to access the sheet. I have tried 10^6 things so I really don't even know what direction to point you in. I'm sorry this is vague but I'm thinking it's something to do with the type of data - I just don't know what to do?

https://www.dropbox.com/s/0ykd10wpf1fau2n/Consolidated MAMU data_Redacted.xlsx?dl=0

Thank you for any insight you can give me!

 

[Canapone]

Hi,

column L houses some text strings (NA). If you'd need them, you could try

=SUMPRODUCT(($A$2:$A$30=U$1)*($I$2:$I$30+0=$T2)*(SUBSTITUTE($L$2:$L$30,"NA",0)))

Without "NA's" SUMPRODUCT should deliver attended results.


Regards

 

[Aladin Akyurek]

What is the SumProduct formula that you have tried?

 

[Aladin Akyurek]

Hi,

column L houses some text strings (NA). If you'd need them, you could try

=SUMPRODUCT(($A$2:$A$30=U$1)*($I$2:$I$30+0=$T2)*(SUBSTITUTE($L$2:$L$30,"NA",0)))

Without "NA's" SUMPRODUCT should deliver attended results.


Regards

If this NA is indeed just NA, not #N/A, SumProduct without any special attention can cope with it...

=SUMPRODUCT(($A$2:$A$30=U$1)*($I$2:$I$30+0=$T2),$L$2:$L$30)

if we are wanting to sum L2:L30.

 

[rdelcarlo]

Thank you - everyones comments were very helpful and it appears that the issue referring to column L mentioning text strings was the issue. I was unaware of the limitations on this formula's syntax. Thank you very much.

 

[Aladin Akyurek]

Thank you - everyones comments were very helpful and it appears that the issue referring to column L mentioning text strings was the issue. I was unaware of the limitations on this formula's syntax. Thank you very much.

The comma syntax which is usual for SUMPRODUCT does allow for text in the sum range just like SUM would. The star syntax cannot cope with text for multiplying numbers with text values are bound to return #VALUE !...