1

I am trying to figure out a formula. I need to know how many times a certain issue happens based on case numbers. Meaning there are multiple entries under a specific case number for that subject, but I only want to count it once. I used the formula below and I can get the total number of entries for that subject; but that is not what I am looking for. This is counting every instance of the word "Custom" showing up.

=COUNTIF(Source!$G:$G,"Custom")

In column Source!N I have case numbers which would be the filter that should only count once. This number will appear everytime someone comments in that case and I do not want it counted multiple times.

Any ideas on how to do this?

  • Did you try searching for "Count unique values among duplicates" at support.microsoft.com? – OSol1tair3 Mar 26 '15 at 17:23
  • Yes, I did and none of them do exactly what I need. I am only a mid-user of excel so some of these formulas are beyond me at this point. – Marc Mar 26 '15 at 18:22
  • Perhaps you could clarify your question a little. It appears Engineer Toast and I are maybe not exactly sure of your desired outcome. When you say, "...but I only want to count it once." What column are you looking to be counted, G or N? You say you need to count how many times a certain issue happens based on case numbers, but then only want to count what only once? Sorry, just trying to get a little clarification. I don't think your problem is hard to solve, I just want to make sure I have good information to help you out. – OSol1tair3 Mar 26 '15 at 18:37
  • Can you paste images in here? Would be a lot easier if I could show you. – Marc Mar 26 '15 at 19:22
  • You need a little more rep to paste images, but post links in a comment to any file sharing service you use and someone will embed them for you. If you don't have one, there are many free ones, like imgur. – fixer1234 Mar 26 '15 at 19:49
0

If Column N contains numbers you can use this array formula to get a count of different numbers where col G = "Custom"

=SUM(IF(FREQUENCY(IF(Source!G1:G1000="Custom",Source!N1:N1000),Source!N1:N1000),1))

confirm with CTRL+SHIFT+ENTER

Edit:

If you are doing this in google sheets you need to wrap the formula in arrayformula function.......and you don't have any instances of the word "Custom" alone in column G, so if you want to search for "custom" amongst other cell contents you need a slightly revised version like this

=arrayformula(SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Custom",Source!G1:G2000)),Source!N1:N2000),Source!N1:N2000),1)))

That formula gave me a result of 24

If you are using Excel take out the arrayformula part and use just

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Custom",Source!G1:G2000)),Source!N1:N2000),Source!N1:N2000),1))

confirm with CTRL+SHIFT+ENTER

  • The Excel one worked. This is great. Thank you so much. – Marc Mar 27 '15 at 12:30
1

It sounds like you want to count all the unique values for which column G:G is "Custom". In that case, the formula is found through a quick Google search:

=SUM(IF(Source!$G$1:$G$1000="Custom", 1/(COUNTIFS(Source!$G$1:$G$1000, "Custom", Source!$N$1:$N$1000, Source!$N$1:$N$1000)), 0))

This is entered as an array formula with Ctrl+Shift+Enter

Note that you can't reference an entire column in an array formula. Instead, just pick some range that is larger than your source data will ever be. For the example, I chose the first 1,000 rows. If you need to change this, be sure to change it for every range reference as they all must be the same size.

  • Thank you very much for your quick reply. I have searched several times in google and was not able to find it. I copied and pasted the formula that you pasted above and it returns a 0 so something isnt quite right. Any idea what else it could be? – Marc Mar 26 '15 at 18:16
  • This works for me - as long as you don't have any rows that contain "custom" in G but a blank in N - in that case you'd get #DIV/0! error - did you use CTRL+SHIFT+ENTER - but you could try my suggestion too - it should be more efficient if you have numbers in col N – barry houdini Mar 26 '15 at 18:49
  • OK here is what I am looking at. These columns are on a tab called Source. Subject is (G) and Case Number is (N). In this instance you see three that are to case 435868 and one that is case 446469. In this instance what I am looking to see is 2. Cant paste images. Woudl make it easier to show. – Marc Mar 26 '15 at 19:13
  • I mocked it up and got the correct answer, 2. If you don't use an array formula, it doesn't sum properly so it only returns the first value from the array it forms for the calculations. Double-check that you entered is as an array formula. If you did, you'll see curly brackets { } get added to the beginning and end of the formula. My mockup was as follows: Data inG1:G7: Custom, Custom, Custom, Custom, Bob, Bob, Ggog. Data in N1:N7: 435868, 446469, 435868, 446469, 435868, 17, 446469. – Engineer Toast Mar 26 '15 at 20:14
  • Did you try this on the link that I posted? drive.google.com/file/d/0BzW9He8xcf0GdThPSHYtM3dnVWs/… if so can you send me the actual formula that you used? If you look at Sheet2 that is where I am trying to calculate. I need to get this done today and I am running out of time. I really appreciate all of your help. – Marc Mar 26 '15 at 20:24

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.