i have a list of customers that I need to count the corresponding "LK" tasks in column E. However the customer id's are in column G but will only appear 1 row above the "LK" tasks. im trying sumproducts with index match and -1,2 but cant get it to work.
All "LK" tasks will appear after a task with a customer number.
2-9-2018 13-26-44 PM.png
=COUNTIFS($E$2:$E$999,O$1,$G$1:$G$998,$N2)
in O1 LK
Pivot table or COUNTIFS() function, depending on which you prefer. The pivot table will have the flexibility to filter or summarize other quantities, if you will want the flexibility.
I'm not sure why you have 0's in the customer_id field for the LK tasks. I would go through the data and enter the correct customer number in those records. This could be a simple helper column =IF(G3=0,G2,G3) formula, then use that helper column as the real customer_id column, or copy->paste special->as values to fill in the existing customer_id column.
That's how I'd do it -- clean up the data base so that each record has the correct customer_id number, then use COUNTIFS() or a pivot table.
Originally Posted by shg
The database will not capture customerID on any LK task due to.....well, 80's technology
This simple yet effective formula did the trick.
Thanks Tim
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks