I have a sheet that is used for tracking sales leads. Each row is a unique lead with a few of the first columns being basic information like Account #, Type, Date Signed Up, etc. Then I have about 16 columns that follow and are used for tracking contact days and their outcome, formatted as [Attempt1][Outcome1]. I have to report on these numbers in a few different ways and I'm trying to create a formula that will count certain outcomes. As of now, I have a behemoth =SUM(COUNTIF()) formula created, and it works for the metric of how many attempts were made. It looks at the 8 pairs of [Attempt#][Outcome#] and counts every time there is a date that falls in a certain date range and it's associated outcome cell is not empty.

The issue I'm having is when I need to report on a slightly more specific metric of "how many customer's were pitched during this timeframe". The criteria for whether or not a client is pitched is simple, the outcome cell will denote it as "Call" and this only needs to happen once for it to count, but it is very common for this to happen multiple times within the date range. Right now, it is counting every cell that meets this criteria and it inflates the number quite a bit throwing off other stats.

My thought was to use IF statements throughout to be able to terminate the entire formula if it comes across one match. But I think that's going outside of what I know how to do. I wanted to see if there were better answers out there and I had a hard time finding other examples that closely mirrored my situation.

  • 1
    You haven't given us any data or ranges to look at. But it sounds to me like you simply need to use COUNTUNIQUEIFS( ). Look into that. Or share a link to your spreadsheet (or a copy of it, or a sanitized copy of it, or a representative sample from it that contains enough data to illustrate the goal).
    – Erik Tyler
    Nov 15 at 20:47
  • HI. it is very common for this to happen multiple times within the date range. In this situation, it sounds like you need to find the latest date. I'm thinking that a query or filter might be an option. On a tangent, you mention SUM(COUNTIF()) - what are you summing, the counts? An alternative might be countifs. But as noted by @ErikTyler, we're shooting in the dark unless you supply some data (real or dummy) to show the layout that you're using.
    – Tedinoz
    Nov 17 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy