Having Trouble Finding Values

[rcb007]

I have the table to the Left which has all the data in a vertical form. I am trying to reformat it to the style to the Right, which is horizontal.

The formula I am using is

=IF(Q3="","",(SUMIFS($O$2:$O$1000,$M$2:$M$1000,Q3,$N$2:$N$1000,$R$2)))

It seems that everything kinda works with the above formula except for the Values in Q13 and Q16 which has a Number and Letter Value.

Or is there a better way to organize this data?

Thanks for the help.

ID	COVERAGES	AREA			LOOKUP TABLE
		0.00​		ID	Impervious	Pervious	Roof
200	Pervious	1.35​		0	0.00	0.00	0.00
201	Impervious	0.35​		200	0.00	1.35	0.00
201	Pervious	0.02​		201	0.35	0.02	0.00
202	Roof	0.17​		202	0.00	0.00	0.17
203	Roof	0.11​		203	0.00	0.00	0.11
300	Roof	0.53​		300	0.00	0.00	0.53
301	Impervious	0.17​		301	0.17	0.55	0.25
301	Pervious	0.55​		302	0.10	0.32	0.00
301	Roof	0.25​		303	0.10	0.49	0.15
302	Impervious	0.10​		304	0.09	0.00	0.00
302	Pervious	0.32​		304A	0.00	0.00	0.00
303	Impervious	0.10​		305	0.08	0.00	0.00
303	Pervious	0.49​		306	0.09	0.23	0.14
303	Roof	0.15​		306A	0.00	0.00	0.00
304	Impervious	0.09​		307	0.14	0.09	0.00
304A	Impervious	1.60​		308	0.14	0.08	0.00
305	Impervious	0.08​		310	0.17	0.29	0.00
306	Impervious	0.09​		311	0.07	0.13	0.24
306	Pervious	0.23​		312	0.08	0.75	0.00
306	Roof	0.14​
306A	Impervious	0.33​
306A	Pervious	0.06​
306A	Roof	0.14​
307	Impervious	0.14​
307	Pervious	0.09​
308	Impervious	0.14​
308	Pervious	0.08​
310	Impervious	0.17​
310	Pervious	0.29​
311	Impervious	0.07​
311	Pervious	0.13​
311	Roof	0.24​
312	Impervious	0.08​
312	Pervious	0.75​

 

[Fluff]

The fact that those 2 values have a letter shouldn't make any difference.
Check that they don't have any trailing spaces.

 

[rcb007]

That was it... What would be the best method to add a trim into this formula?

=IF(J3="","",(REPLACE(J3,FIND("-",J3),25,"")))

I would assume that would take care of everything working like the others?

 

[Fluff]

Put it just before the Replace

 

[rcb007]

Thank you for the help. It works!

 

[Fluff]

You're welcome & thanks for the feedback.