SUMIFS issues

Tanya B

My formula error #INVALID REFERENCE

=SUMIFS({formula code 1}, {Supervisor}, [Column2]@row, {Program Primary,[Column3]21})

All I need to do is match the supervisor and the program and sum up the formula code 1 column

Paul Newcome

For troubleshooting purposes, what do you get when you use a separate COUNTIFS for each range/criteria set?

=COUNTIFS({Supervisor}, @cell = [Column2]@row)

=COUNTIFS({Program Primary}, @cell = [Column3]$21)

Do either of those show a zero when there should be a count?

NickStaffordPM

Your issue is with the "

 {Program Primary,[Column3]21})

"

Part of your formula

I am unsure what Column2 is as well. But becuase this is a "criterion_range2" appears incomplete and is without a "criterion 2", which I believe is giving you this error.

For next steps, please either provide a full picture of your sheet with confidential information blocked or use the GenAI features on smartsheet or ChatGPT to provided a more specific formula alteration.

Let me know if there is anything else I can help with :)

Tanya B

I did notice it did not pick up the row number…I tried to modify and I'm closer…but getting 0 when I should have a value. Full screen shots bnelow

Tanya B

Paul Newcome

For troubleshooting purposes, what do you get when you use a separate COUNTIFS for each range/criteria set?

=COUNTIFS({Supervisor}, @cell = [Column2]@row)

=COUNTIFS({Program Primary}, @cell = [Column3]$21)

Do either of those show a zero when there should be a count?

Tanya B

I got it work - I'm finding when I follow the prompts for formulas in Smartsheet it does not always put you in the correct spot, often will have to arrow over and add my next comma or sometimes does not put in the cell number :(

This was the winner

=SUMIFS({Manpower Plan Range 2}, {Manpower Plan Supervisor}, [Column2]22, {Manpower Plan Program}, [Column3]21)