Formula for Counts Using Two Separate Columns on Different Sheet
Hello,
I am trying to create a formula to pull information from the attached page (Armorel In-Process Record).
I need the formula to count the number of times each inspector makes an entry utilizing the column labeled [Inspector Name].
I then want the formula to restrict the dates viewed to the last 7 days by utilizing the column labeled [Date].
I appreciate everyone's help.
Best Answer
-
Hi Leland,
You can do this with a COUNTIFS formula that's using Cross Sheet References.
Instead of building a new formula per-inspector, I would suggest creating a sheet that has one column with all the inspectors' names, and one column that will have your formula per-person.
Then you can use something like this:
=COUNTIFS({Inspector Name other Sheet}, [Inspector Name]@row, {Date Column other Sheet}, >=TODAY(-7), {Date Column other Sheet}, <=TODAY())
Keep in mind that you would need to have the Inspector Name on both sheets match identically for it to find each criteria.
Here are some articles I used to help build this... let me know if I can clarify anything further:
Cheers!
Genevieve
Answers
-
Hi Leland,
You can do this with a COUNTIFS formula that's using Cross Sheet References.
Instead of building a new formula per-inspector, I would suggest creating a sheet that has one column with all the inspectors' names, and one column that will have your formula per-person.
Then you can use something like this:
=COUNTIFS({Inspector Name other Sheet}, [Inspector Name]@row, {Date Column other Sheet}, >=TODAY(-7), {Date Column other Sheet}, <=TODAY())
Keep in mind that you would need to have the Inspector Name on both sheets match identically for it to find each criteria.
Here are some articles I used to help build this... let me know if I can clarify anything further:
Cheers!
Genevieve
-
Hi Genevieve!
Thanks so much for your response!
I entered in the formula as you described and get the #UNPARSEABLE error
I've attached a screenshot of the formula I entered and the new sheet as you described, as well as a screenshot of the sheet being referenced.
Can you please review and let me know what I am doing wrong? I assume anything that says 'other sheet' needed me to reference the column from the other sheet. Everything else in the formula would be entered manually correct?
Sorry just trying to make sure I didn't create a simple error that resulted in the issue.
Thanks again for your help!
-
Hi Leland,
No worries at all, I'm happy to help!
I think I see what's going on here - so you've put the exact person's name [in these] but that should actually reference the column name... in your case "Inspector Name"
Try taking out Eric's name from the square brackets and have [Inspector Name]@row instead. The formula should light that up in a specific colour to show it's found the correct column and cell.
Then you can copy/paste that formula or drag-fill it down the whole column. The @row gets it to look at the contact in that row, in the [Inspector Name] column... make sense?
Let me know if it works!
Help Article Resources
Categories
Check out the Formula Handbook template!
