SumIFs formula #INVALID DATA TYPE error

ShahA

Getting the #INVALID DATA TYPE error with this formula:

=SUMIFS({Forecasts (SS) Hours}, {Forecasts (SS) Consultant}, $[Consultant Name]23, {Forecasts (SS) Start}, WEEKNUMBER(@cell) = 48, {Forecasts (SS) Cancelled}, 0, {Forecasts (SS) Start}, YEAR(@cell) = 2019)

What's frustrating is if I change the ${Consultant Name]23 to a different number, say 24, it works. Also if I change the name in the column in cell 23 to something else, it works too. This particular name seems to be giving the error. This sheet references another sheet that contains the names of the Consultants, however, I can't find anything wrong with the name. I've tried deleting and re-adding the name to both sheets. 

 

Thank you!

 

Andrée Starå

Hi Shah,

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

This might help: 

#INVALID DATA TYPE

Cause

The formula contains or references an incompatible data type, such as =INT("Hello")

Resolution

Make sure the formula references the correct data type.

Paul Newcome

Try wrapping both the WEEKNUMBER and YEAR functions in an IFERROR.

 

I am initially thinking that it isn't so much the consultant name itself, but the data that is pulled for that consultant.

 

If there are any blanks or non-date values in any row for that consultant, you will get the error.

 

=SUMIFS({Forecasts (SS) Hours}, {Forecasts (SS) Consultant}, $[Consultant Name]23, {Forecasts (SS) Start}, IFERROR(WEEKNUMBER(@cell), 0) = 48, {Forecasts (SS) Cancelled}, 0, {Forecasts (SS) Start}, IFERROR(YEAR(@cell), 0) = 2019)