Summary Sheet Formula - conditions

SueinSpain

Hi Wizards,

I use a lot of formula including summary sheet formula for use in dashboards.

SUMIF and CountIF are great but here is my problem (bear with the long explanation)

3 departments fill in a daily form and select Area (Stoke, CC or Transport) and then provide 4 or 5 pieces of information all through a form. This then populates the smartsheet but with separate fields for Stoke, CC & Transport. Therefore a row in the smartsheet will only have a few fields completed.

After a lot of trial and error I can calculate a helper field in the smartsheet called [Yesterday] with values "True" or "False" and this works fine.

Summary sheet formula are calculated for example

=SUMIFS([Stoke DC LPH]:[Stoke DC LPH], Yesterday:Yesterday, ="True")

and all works perfectly as just 1 value returned.

HOWEVER, I also have a comments field that I want to pick up into a summary field (or via a report or anyhow really) and there will be one value where [Yesterday] = "True" and [Area] = "Transport" etc. but I cannot seem to get this to work.

I've tried =IF(AND(Yesterday:Yesterday = "True", Area:Area = "Transport"), [Transport Comments], "") but I get invalid or unparsable etc.

I know I'm missing the obvious but just cannot get it to work

Alternatives as I just want to get this field value on the dashboard for the specific day (Yesterday = "True") and specific dept (Area = "Transport") - do I try 3 daily reports with the filters to match and just add to the dashboard.

Please help a very confusted and frustrated admin

Many thanks

Sue

Paul Newcome

Try an INDEX/COLLECT

=INDEX(COLLECT([Transport Comments]:[Transport Comments], Yesterday:Yesterday, @cell = "True", Area:Area, @cell = "Transport"), 1)

Paul Newcome

Happy to help. 👍️

Paul Newcome

Try an INDEX/COLLECT

=INDEX(COLLECT([Transport Comments]:[Transport Comments], Yesterday:Yesterday, @cell = "True", Area:Area, @cell = "Transport"), 1)

SueinSpain

@Paul Newcome you are a genius yes this works perfectly thank you so much

I just couldn't see this way of looking at at

Paul Newcome

Happy to help. 👍️