-
COUNTIFS using today's month
Hi I'm currently trying to count the number of jobs, matching a specific work type when the received date matches today's month. I am referenced another sheet (Job Log) also. The formular I am currently using is: =COUNTIFS({Job Log l Job Type}, [Job Type]@row, {Job Log l Office}, "Birmingham", [{Job Log l Date Received},…
-
Find first non-blank cell with INDEX/MATCH
Hi guys! I'm pulling 2 columns, each with multiples of the same value, and some rows have blanks in the cells until the reference sheet is updated with said information. My INDEX/MATCH works great, except there it pulls the blank values, and thereby reducing the accuracy of my sheet. The formula is as follows;…
-
Autofill contact details
Hello all, I'm trying to create a formula to "Autofill" contact (Name, phone number & Email), What is the smartest, fastest way to create a formula for over 500 contacts? Thank you
-
Moving data from client report to my template
Hi - I'm importing client data to a template and I am using INDEX and MATCH formula =INDEX({Crane Range 6}, MATCH([Contra Firm / Sponsor Acct #]@row, {Crane Range 2}, 0)) This works well for one column at a time. I want to capture multiple columns at once so I'm not copying first name, middle name, last name, street…
-
How do I display counts of all of the dropdown options used on one sheet and possibly multiple?
Long story short, I'm building a system to track pending legislation, and for each bill that posts, team members are assigned it to one (or more) categories in a CATEGORY dropdown column. I'm working on a dashboard of high-level views, and I'm trying to figure out the most efficient way to roll-up and display how many (and…
-
What formula to use to count all instances of a value across multiple columns.
Hello, My colleagues have set up a sheet to help them track sustainability actions in their department. They've asked me to help with setting up reports and a dashboard that summarise and calculates the types of actions across the 4 Action type columns, as well as summarising further by team. As you can see there's been an…
-
Is there a Formula to Insert Rows Based on Criteria?
I have 2 sheets, 1 that holds subsets of tasks, and a master sheet where work is assigned. If I want to add a subset of tasks to my master sheet based on criteria in another column, how would I achieve that? So if in my master sheet LP | Legacy Site Landing Page w/o Form is selected from the dropdown, the formula would…
-
MAX, COLLECT, MATCH Formula
I have a formula that is giving an "incorrect argument" error. =MAX(COLLECT({_Training Database Range 1}, MATCH([Primary Column]@row, {_Training Database Range 2}, 0), {_Training Database Range 3}, "ROVER - SITE CALIBRATION"), 0) I am trying to match a name for from a column (sheet 1) to a column in another sheet (sheet 2)…
-
How to use Countifs for rows that start with a certain character?
I am trying to use countifs formula. I want it to return how many there are that meet two criteria- the first being the first digit of a three digit number in one column is 1--. It would also have to be "Completed" in another column. =COUNTIFS({Number}, HAS(LEFT(@cell, 1)= "1:"), {Status}, "Completed")
-
Filter the Absolute Row Number
Hello, I have an odd request. Is there a way to have a filter so that it always shows the first 25 rows of a sheet and hides everything else below that number. I'm talking about the absolute row number. It needs to be a filter. We have essentially a list of tasks that are sorted by a number of criteria. I want my team to…
