-
Dashboard formula counting rows within date range
First post, Longtime SSheet user but new to formulas and stuck on this last one for a dashboard. I am trying to count rows where the due date is within a range, the form created date is within 14 days of the due date and the status is not "complete." I have tried a wide variety of solutions. =COUNTIFS({FAMS Marketing…
-
Nested Sumif Formula
Hello, While we're training new employees I need to track their progress over a 2 week period using 9 different forms that are filled out at the end of each day; which used to be done on paper. There are 4 different teams that send in trainees so I thought it'd be easier to create 4 different workspaces for each team,…
-
Help with rollup sheet structure and formulas
We have contractors that we pay every 2 weeks. I created a form for them to upload invoice info into. I also created a report that pulls the individual invoice totals. What i want to create is a rollup sheet that will give me a total of that week's invoices for a given contractor. So for instance...Total invoiced amount…
-
COUNTIFS + FIND function help
Hello! I am trying to count the number of rows with a non-blank cell in one column and a blank cell in another column. The non-blank cell contains a contract number that looks like 18-A-001-SH and everything but the leading 18 changes from one row to the next. I started with this formula but it is returning a value of zero…
-
Formula for when if a checkbox is checked...or not
What would be the formula that would return "True" if a checkbox is marcked as checked and "False" if it is not? Thank you!
-
Changing column Property Status (Red, Yellow, Green) when two columns dates are different
I am in need for the status colum 1 (red, yellow, green) to automatically change when column 2 (date) is passed column 3 (date). Can this be done with a formula? I can do "=IF([Approved Required Delivered Date]1 < [Adjusted Delivery Date]1, "Red")", however, I am sure on how to add the other two conditions in the formula.…
-
"SUMIFS=" " Circular Reference Error"
Hello, I figured out how to make work a large formula for =Sumifs. The idea was to add the "target budget" of all projects that would have as Project Manager (PM/RI) "Mari M." and only for the projects that would have an "Status of Request" of ( New, Work in Progress, In Design, In construction, Substantially completed,…
-
Finding the child rows of a parent row
We keep track of team projects on 1 smart sheet. Projects are in parent rows. Task related to these projects are in children rows. We also have ongoing tasks (newsletters, etc) that have a due date of 12/31/19. Here is the formula to determine the number of ongoing projects: COUNTIFS({Project List Range 1}, "Project",…
-
SUMIFS Based on Parent and Children Criteria
Hi there! I am trying to make a master reporting sheet that is just a sum of another source sheet based on specific criteria of parent and child rows. It's a two step SUMIFS that I can't seem to get straight. It would need to be a sum "grand total count", if hierarchy 0 month = specified month and hierarchy 1 matches…
-
Formula help with Sum ifs
Hi, I have done a formula for Countifs =COUNTIFS({July Range unders}, <0, {July Range Manager}, [A/M]1) This works well and returns the correct data, however i now want the total sum of these entries so tried the following: =SUMIFS({July Range unders}, <0, {July Range Manager}, [A/M]1) But this is pulling back an Incorrect…
