How do I use the attachment column in a Sheet Summary Formula?

Carroll Wall

I want to write a sheet summary formula that counts how many rows where the MoSCow column contains M, have attachments.

The syntax I am using is COUNTIFS( range1, criterion1, [ range2​,criterion2​]).

I have used this formula many times

Getting the column criteria (range 1, criterion 1)specified is straight forward.

But I cannot figure out how to add a range using the "Attachments" column. If I click the column in a row, the window opens. I can hand-type it in BUT what criterion do I use to show an attachment exists? 1 for exists and 0 for does not exist?

=COUNTIFS(MoSCoW@row,"M",[Attachments@row, 1]) gives me "unparseable"

Any hints? Thanks!

Carroll

Andrée Starå

Hi @Carroll Wall

I hope you're well and safe!

You could use a Workflow with the change of a cell action triggering from attachments being added combined with a helper column to indicate if the row has an attachment and then reference it in the formula.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

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

Paul Newcome

There is no way to get this to work using a formula. You would need to use the API for this.

Carroll Wall

Is the API the *only* way to create visibility on which rows have attachments and which don't?

Paul Newcome

There may be other 3rd party apps such as Zapier. I am not sure about 3rd party apps, but I can say that the API is able to count attachments.

Carroll Wall

Wow, ok, thank you!

Toufong Vang

Since you just want to count the number of rows that have attachments, here's a workaround to having to implement a third-party tool.

In this approach, you use workflow automation to move a row from the source sheet into a target sheet when (1) an attachment is added to the row, and (2) the row isn't already marked as having an attachment. Once in the target sheet, a "1" is placed into a helper column and then the row is immediately moved back to the source sheet. You can count or sum the helper column to get the number of rows that have attachments.

Here's how to set that up...

I. CREATE YOUR 'TARGET' SHEET

1. Create a new blank sheet and delete all of the columns except for the "Primary Column". In this example, we'll call this target sheet, "Sheet 1B".

2. Save the sheet.

3. Open your source sheet.

II. PREPARE YOUR 'SOURCE' SHEET

4. Create an autonumber column in this sheet, if you don't already have one. (In the screenshots below, I called this "myAutoNumber".)

5. Create a helper column and name it like "HasAttachment".

6.1. Create a workflow automation that is triggered when "An attachment is: xAdded".

6.2. Set the condition to "Where 'HasAttachment' is not one of '1'".

6.3. Set the action to "Move rows", "Move to: Sheet 1B".

6.4. Save the automation.

7. Copy a row from the source, Sheet 1A, into the target, Sheet 1B.

8. Close the source sheet and open the target sheet.

III. PREPARE THE TARGET SHEET

9. In the column "HasAttachment" create the column formula =IF(myAutoNumber@row<>"", 1,1).

10. Create a workflow automation that triggers when a row is added or changed and "When myAutoNumber changes to: 'Any Value'".

11. Set the action to "Move rows 'Move to: Sheet 1A'".

12. Save the automation.

13. Delete the all of the rows in the target sheet.

To test the automations, add an attachment to any row in the source sheet.

For rows that existed before you created this automation setup, you will have to enter "1" into the "HasAttachment" column, OR add a new attachment to the row, OR manually move the row into the target sheet.

Carroll Wall

Thanks for looking at this and offering assistance!

I am not clear on the need to move the row from source and then back.

Could it not just be copied to target and then deleted? (Same triggers, different actions).

Also, I am not clear on how the count is generated in the target sheet if no rows ever stay there?

Thank you again!

Carroll

PS If I don't reply right away, it is because I will be away from my computer for a few days.

Andrée Starå

Hi @Carroll Wall

I hope you're well and safe!

You could use a Workflow with the change of a cell action triggering from attachments being added combined with a helper column to indicate if the row has an attachment and then reference it in the formula.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

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

Carroll Wall

Andree, thank you.

This is a quick short-term work around.

Hope your year-end festivities are joyful!

Carroll

Andrée Starå

@Carroll Wall

Excellent!

Happy to help!

Thanks, and I hope yours are too!

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