Losing my mind...

Paige_Hamby

I've been trying to figure out what to do with this formula and decided I've been staring at it long enough on my own. I'm trying to count the YTD total of resolution types of customers with Pick Planner and License Plate function types (customer has to have BOTH). I was able to use a similar formula for YTD total of customers with pick planner and license plate function types and it gave me the exact amount I was expecting. But the resolution type formula is counting wayyy more than it should.

YTD total of customers with pick planner and license plate: =IF(OR(COUNTIFS({Function Type}, "Pick planner", {Customer Name}, [Customer Name]@row, {Year}, [Current Year]#) = 0, COUNTIFS({Function Type}, "License Plate", {Customer Name}, [Customer Name]@row, {Year}, [Current Year]#) = 0), "", COUNTIFS({Function Type}, "Pick planner", {Customer Name}, [Customer Name]@row, {Year}, [Current Year]#) + COUNTIFS({Function Type}, "License Plate", {Customer Name}, [Customer Name]@row, {Year}, [Current Year]#))

YTD total of resolution types of customers with Pick Planner and License Plate function types: =IF(OR(COUNTIFS({Function type}, "Pick Planner", {Resolution type}, [Resolution Type]@row, {Year}, [Current Year]#) = 0, COUNTIFS({Function type}, "License Plate", {Resolution type}, [Resolution Type]@row, {Year}, [Current Year]#) = 0), "", COUNTIFS({Function type}, "Pick Planner", {Resolution type}, [Resolution Type]@row, {Year}, [Current Year]#) + COUNTIFS({Function type}, "License Plate", {Resolution type}, [Resolution Type]@row, {Year}, [Current Year]#))

Example: Code resolution type should be 45, but with the above formula I'm getting 154.

Ryan Kramer

@Paige_Hamby ,

From your provided formulas, I can see that the first one counts the number of customers who have both "Pick planner" and "License Plate" as their function types for the current year. If any of these function types are missing for the given customer for the current year, it returns an empty string.

The second formula you've given, however, counts the number of resolution types associated with "Pick Planner" and "License Plate" function types for a given resolution type and year.

Here's where the problem might lie:

Let's say Customer A had 5 incidents with "Pick Planner" function type and 5 incidents with "License Plate" function type in the current year. Each of these incidents could have a unique resolution type.

With the first formula, you'd get a count of 10 for Customer A because it adds the count for both function types.

In the second formula, if you're calculating the YTD total of a specific resolution type (let's say 'Resolved'), and each of the 10 incidents for Customer A had this resolution, you'd still get a count of 10. This may not seem wrong at first, but if there are many customers and each has multiple incidents with the desired function types and resolution type, your number will inflate quickly.

Here's a proposed solution:

To get the YTD total of resolution types for customers with both "Pick Planner" and "License Plate" function types, you should first ensure that the customer has both function types in the current year. Once confirmed, then tally the resolution types for that customer.

Try this adjusted formula:

=IF(OR(COUNTIFS({Function Type}, "Pick planner", {Customer Name}, [Customer Name]@row, {Year}, [Current Year]#) = 0, COUNTIFS({Function Type}, "License Plate", {Customer Name}, [Customer Name]@row, {Year}, [Current Year]#) = 0), "", COUNTIFS({Function type}, "Pick Planner", {Resolution type}, [Resolution Type]@row, {Customer Name}, [Customer Name]@row, {Year}, [Current Year]#) + COUNTIFS({Function type}, "License Plate", {Resolution type}, [Resolution Type]@row, {Customer Name}, [Customer Name]@row, {Year}, [Current Year]#)) 

The difference here is that I've added a {Customer Name} criterion to your resolution type COUNTIFS. This ensures you're counting resolution types only for customers that have both "Pick Planner" and "License Plate" function types for the current year.

Give it a shot and let me know if it works.

Ryan