SumIFs formula #INVALID DATA TYPE error

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!

Find more posts tagged with

Formulas

Comments

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)

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.