Best Of
Re: COUNTIFS with MONTH
Hi @Megan T
Try something like this.
=COUNTIFS([Retest Result]:[Retest Result]; "Pass"; [Retest Date]:[Retest Date]; IFERROR(MONTH(@cell); 0) = MONTH(TODAY())) + COUNTIFS(Results:Results; "Pass"; [Final Date]:[Final Date]; IFERROR(MONTH(@cell); 0) = MONTH(TODAY()))
The same version but with the below changes for convenience.
=COUNTIFS([Retest Result]:[Retest Result], "Pass", [Retest Date]:[Retest Date], IFERROR(MONTH(@cell), 0) = MONTH(TODAY())) + COUNTIFS(Results:Results, "Pass", [Final Date]:[Final Date], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.
Did that work?
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 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!
Re: Vlookup Output doesn't appear in report
Can you provide some more information on which fields are vlookup and what is not showing up in the report vs. the sheet?
Maybe highlight the cells in the sheet and report.
In general, things to check:
- Report filters to make sure you have no filtered anything out
- If you renamed a field in a sheet after selecting it in a report, you will need to go to the report and reselect it
Make sure you're pointing at the correct sheet
Cheers,
Ramzi
Re: show only date vs date time using Concatenate formula
Try this:
=LEFT([Start Date]49, FIND(" ", [Start Date]49)) + "-" + LEFT([Actual End Date]49, FIND(" ", [Actual End Date]49))
Hope that helps.
Cheers,
Ramzi
Re: Sumifs for Multi Select Drop Down
Hi @Joy Kwan
You're correct: you can either add together different SUMIFS to find each SUM per-individual-task (which may end up with duplicates), or you can create a SUMIFS formula that will look for certain combinations only, but it won't find the individual task on its own.
You could write into your formula all the possible combinations of selections and minus that off of your total, but this may get too complex, depending on how many criteria you have. Another option would be to SUM if the Task Type column does not have a specific selection, would that work for you?
Ex:
=SUMIFS([columns2]:[column2], [task type]:[task type], NOT(HAS(@cell, "N/A")))
Re: How to autopopulate today's date in a column
Answer to your other question.
Also I am wondering why forumlas can do things 10x more complex than that (I know because I have them in my sheets) they couldn't do this?
Unfortunately, no, because there isn't a timestamp/datestamp formula function or similar at the moment.
Re: Autocomplete date range
Assuming that Week 1 start in 2020 was 12/31/2019 replace your formula with this:
=COUNTIFS({Enquiry Tracker (V1) Range 4}, <=(DATE(2019, 12, 31) + ([Week Number]@row - 1) * 7+7), {Enquiry Tracker (V1) Range 4}, >=(DATE(2019, 12, 31) + ([Week Number]@row - 1) * 7))
If this works for you, copy the formula into all cells in the column and it should work based on week # value.
Better yet convert the formula to a column formula (newest Smartsheet feature) and you won't even have to copy it.
I hope this works or you.
Cheers,
Ramzi
Re: Is there a way to assign a certain task/activity to multiple groups and generate report by group?
Hi @Chanitra K.
Would it work by adding multiple contacts to a contact column or multiple dropdown options in a dropdown list column?
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!
Re: Permission Level
@Corentin Dal Farra I believe so, if a user has two levels of permissions to the same workspace under different groups, the higher will be selected. In your example above, admin level will be the one selected.
Re: Not sure why my referenced cell moves down when I select the next row (see pic)
You need to use the '$' to keep the reference cell the same.
This is referred to as 'Fixed' or 'Absolute' reference
You can use it before both the column and row number, i.e. [Assigned To]$43 in your picture