I have something wrong in MONTH retreival

Chris Rolando

I am attempting to return a series of values ONLY for the month in which this is being viewed.

SHEET 1

The "Count", The "Goal" and the "Count - Goal" are all coming from another sheet.

Where I am getting in trouble is I have been using Countifs, and I believe this is incorrect.

Here is the clunky formula I came up with:

=COUNTIFS({GOAL Active SOW 2022 from Project Sheets Range 1}, [Primary Column]@row, ({GOAL Active SOW 2022 from Project Sheets Range 2}), AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Okay now onto the other sheet:

SHEET 2

What I am TRYING to do is: On SHEET 1, return the actual number in "Completed",

IF The item in "Reference" on Sheet 2 matches the item in "Primary Column" on Sheet 1, AND The Date is "Start Date" on Sheet 2 is this month, this year, THEN report the number in TOTAL COMPLETE on Sheet 2 to "COUNT" on sheet one.

From there I can do the same to draw the goal number in and so forth.

SO if someone can help me out and tell me where to send the bottle of bourbon to... I would be MOST appreciative!

Mike TV

@Chris Rolando

Try this:

=INDEX(COLLECT({GOAL Active SOW 2022 from Project Sheets Total Com}, {GOAL Active SOW 2022 from Project Sheets REFERENCE}, [Primary Column]@row, {GOAL Active SOW 2022 from Project Sheets Start Dat}, MONTH(@cell) = MONTH(TODAY()), {GOAL Active SOW 2022 from Project Sheets Start Dat}, YEAR(@cell) = YEAR(TODAY())), 1)

Chris Rolando

This morning I tried a new sting to get my answer:

=IF({GOAL Active SOW 2022 from Project Sheets Range 2} = MONTH(), IF({GOAL Active SOW 2022 from Project Sheets Range 1} = [Primary Column]@row, {GOAL Active SOW 2022 from Project Sheets Range 3}))

This too failed. In this one, Range 2 is the "Start Date" field in Sheet 2. Range 1 is the Reference in Sheet 2. Range 3 is the number I wish to return to Sheet 1 in the Count column.

If this was Excel I would have this answer. SmartSheet just some times makes thing a bit hard for me to understand.

Chris Rolando

I have now tried INDEX COLLECT

=INDEX(COLLECT({GOAL Active SOW 2022 from Project Sheets Total Com}, {GOAL Active SOW 2022 from Project Sheets REFERENCE}, [Primary Column]@row), {GOAL Active SOW 2022 from Project Sheets Start Dat}, MONTH(TODAY()))

Mike TV

@Chris Rolando

Try this:

=INDEX(COLLECT({GOAL Active SOW 2022 from Project Sheets Total Com}, {GOAL Active SOW 2022 from Project Sheets REFERENCE}, [Primary Column]@row, {GOAL Active SOW 2022 from Project Sheets Start Dat}, MONTH(@cell) = MONTH(TODAY()), {GOAL Active SOW 2022 from Project Sheets Start Dat}, YEAR(@cell) = YEAR(TODAY())), 1)