Forumla help: pull over number based on date

Sean Murphy

@Paul Newcome Hey Paul, reaching out for your help again. I've got a field log sheet that I'm hoping to automate. I've figured out how to count the number of panels erected automatically, but now I can't figure out the number of men column. The number of men/day stays here's a published sheet.

https://app.smartsheet.com/b/publish?EQBCT=23c4d7b64cf243ab9f34d0ce2c190ca1

The challenge is it's not really doing any counting or math, just pulling over a number from the #men column, here's a filtered view of 4/16. I only want 8 to automatically be pulled into the number of men/day column for the day the work was done, its not adding or counting.

Andrée Starå

Happy to help!

Ok.

I've added this one instead.

=IFERROR(INDEX([# Men]:[# Men], MATCH([Erection date]@row, Erect:Erect, 0)), "")

Did it work?

Andrée Starå

Ok.

I've added it to one of the columns that weren't locked.

=IF(COUNTIFS(Erect:Erect, [Erection date]@row) = 0, "", COUNTIFS(Erect:Erect, [Erection date]@row))

Andrée Starå

Hi Sean,

I’ve added the formula to your sheet and also updated the formula for the ”Number of Panels erected by day”.

I've simplified the formula with @row, and also I removed the @cell because it's not needed.

For the Number of men/day I used:

=SUMIF(Erect:Erect, [Erection date]@row, [# Men]:[# Men])

For the Number of Panels erected by day I used:

=COUNTIFS(Erect:Erect, [Erection date]@row)

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Sean Murphy

@Andrée Starå Thank you. The simplification helps, but the sumifs is not what i'm looking to accomplish. The way the same number of men erected the panels on the day so the number should only be 8 on 4/17

Andrée Starå

Happy to help!

Ok.

I've added this one instead.

=IFERROR(INDEX([# Men]:[# Men], MATCH([Erection date]@row, Erect:Erect, 0)), "")

Did it work?

Sean Murphy

That's perfect. Thank you

Andrée Starå

Excellent!

I'm always happy to help!

Sean Murphy

@Andrée Starå one last question, how can I make the number of panels cell be blank instead of zero if none?

Sean Murphy

I unlocked the column

Andrée Starå

Ok.

I've added it to one of the columns that weren't locked.

=IF(COUNTIFS(Erect:Erect, [Erection date]@row) = 0, "", COUNTIFS(Erect:Erect, [Erection date]@row))

Sean Murphy

Thanks!

Andrée Starå

@Sean Murphy

You're more than welcome!