Best Of

@Alicia L

I created data to try and help you dial in your formula. I called one sheet "Source Data" and a second set Summary Sheet. The formula lives on the summary sheet in the # of findings column.

You will note that I have a "Week Ending" column. This was used while constructing the formula to make sure I was capturing the right data in testing. It is not used in the final formula.

Something like this:

=SUMIFS({Source Data | Findings}, {Source Data | Date}, {Source Data | Date} >= [Week Start]@row, {Source Data | Date}, {Source Data | Date} <= ([Week Start]@row + 6))

Best,

Hudson

I made mine work with this statement

=IF(AND([% Complete]1 > 0, [% Complete]1 < 1), "In Progress", IF([% Complete]1 = 1, "Complete", "Not Started"))

Try this instead:

=COUNTIFS([Pending]:[Pending], @cell = 1) + "/" + COUNTIFS([Pending]:[Pending], OR(@cell = 1, @cell <> 1))

UPDATE: I figured it out. I am embarrassed to say it did not occur to me initially to change the 100 to a 1 instead (because without the percentage, that is the full value). Thanks for the help!

I have been trying to manipulate the formula based on your info above but am still having issues. Here is where I left it - it does give me a number (rather than an error) but I have two test rows set up to meet this criteria, yet the formula returns zero as the count

=COUNTIFS(Status:Status, "Tech Assist", [Overall Percent Complete]:[Overall Percent Complete], 100)

It is definitely the overall percent complete part that is tripping it up - if I do just the first part with a COUNTIF it works just fine - I have tried with an equals instead of a comma, with and without a percentage sign, and with and without quotes, all throwing some kind of error. Any other thoughts?

Hey man,

Try this formula:

=IF([ITEM 1]@row = 1, IF(NOT(ISBLANK([ITEM 1 QTY]@row)), [ITEM 1 QTY]@row * 10.4))

This will work only if the check box is checked and there is a quantity in the "Item 1 Qty" cell.

Let me know if it works!

Thanks,

JRR

Thank you both, I've learned so much from this thread and your comments on other threads as I was troubleshooting my issues this week.