# Excel formula help **URL:** \ **Category:** Factual Questions\ **Created:** [October 1, 2013, 6:25pm UTC](https://boards.straightdope.com/t/excel-formula-help/670118 "2013-10-01T18:25:17Z")\ **Posts on this page:** 8\ **Page:** 2
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [October 2, 2013, 3:39pm UTC](https://boards.straightdope.com/t/excel-formula-help/670118/21 "2013-10-02T15:39:07Z")
I think you can do this with a formula _if_ you are OK with counting unique values by date, meaning a value will be counted once in every month it appears. This will handle duplicate values and doesn’t require sorting. Enter the following as an array formula - you have to type Ctrl-Shift-Enter. In this example, the month you are matching is in cell D20. ```auto {=SUM(IF(FREQUENCY(IF($C$1:$C$10=1,IF($B$1:$B$10=D20,$A$1:$A$10)), $A$1:$A$10),1))} ``` If you want to count a value only once when it appears in multiple months, I think you’re screwed. That wouldn’t be easy even in SQL (although at least it would be possible and I don’t think it is in Excel). ---
**Author:** ![Skammer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/skammer/32/143_2.png) [@Skammer](https://boards.straightdope.com/u/Skammer)\ **Post date:** [October 2, 2013, 6:01pm UTC](https://boards.straightdope.com/t/excel-formula-help/670118/22 "2013-10-02T18:01:53Z")
Troutman, you are correct that if a value (these are employee ID numbers) has a ‘1’ in multiple months, I want to count her for each month. I think that’s what you asked. I suspected I wanted to use the FREQUENCY function but I couldn’t figure out how. I can’t work on it right now but I will try later today or tomorrow and see if your solution works. Thanks! penultima - I’ll take a look at your solution also. Thank you. ---
**Author:** ![cmosdes](https://avatars.discourse-cdn.com/v4/letter/c/a587f6/32.png) [@cmosdes](https://boards.straightdope.com/u/cmosdes)\ **Post date:** [October 2, 2013, 8:20pm UTC](https://boards.straightdope.com/t/excel-formula-help/670118/23 "2013-10-02T20:20:47Z")
> [@Skammer](#): > > Troutman, you are correct that if a value (these are employee ID numbers) has a ‘1’ in multiple months, I want to count her for each month. I think that’s what you asked. > > I suspected I wanted to use the FREQUENCY function but I couldn’t figure out how. I can’t work on it right now but I will try later today or tomorrow and see if your solution works. Thanks! > > penultima - I’ll take a look at your solution also. Thank you. Is the problem with the solution I provided that you can’t sort the data or that the data in ColC won’t always be just 1 or 0? If the latter, I’d suggest you just add a column with the formula =IF(C \<\> 0, 1, 0), then use the new column in place of ColC. If the issue is sorting, you can always create a ghost table to do the operations on. ---
**Author:** ![Skammer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/skammer/32/143_2.png) [@Skammer](https://boards.straightdope.com/u/Skammer)\ **Post date:** [October 2, 2013, 8:54pm UTC](https://boards.straightdope.com/t/excel-formula-help/670118/24 "2013-10-02T20:54:24Z")
No, **csmodes** , the problem is that the values in column C (0 or 1) are not constant, so I would have to be constantly resorting data every time the user input changed. The table already has 26 columns and 10,000 rows and growing so I’m afraid constant re-sorting would be too slow, if I could even do it. ---
**Author:** ![JerrySTL](https://avatars.discourse-cdn.com/v4/letter/j/e274bd/32.png) [@JerrySTL](https://boards.straightdope.com/u/JerrySTL)\ **Post date:** [October 3, 2013, 2:06pm UTC](https://boards.straightdope.com/t/excel-formula-help/670118/25 "2013-10-03T14:06:55Z")
As others have said, this is much better suited for a database especially if there are thousands of records. Here’s the basic SQL in an Access database. You could change the field names or use aliases. Select b, Count(a) FROM(SELECT distinct Skammer.A, Skammer.B FROM Skammer WHERE (((Skammer.C)=1))) GROUP BY B; ---
**Author:** ![cmosdes](https://avatars.discourse-cdn.com/v4/letter/c/a587f6/32.png) [@cmosdes](https://boards.straightdope.com/u/cmosdes)\ **Post date:** [October 3, 2013, 7:12pm UTC](https://boards.straightdope.com/t/excel-formula-help/670118/26 "2013-10-03T19:12:05Z")
> [@Skammer](#): > > No, **csmodes** , the problem is that the values in column C (0 or 1) are not constant, so I would have to be constantly resorting data every time the user input changed. The table already has 26 columns and 10,000 rows and growing so I’m afraid constant re-sorting would be too slow, if I could even do it. I just created a table with over 40,000 rows and 30 columns and it sorted in less than a second. I agree constant sorting isn’t ideal, though. If the data is accessed and updated frequently, sorting is a bad option. ---
**Author:** ![Skammer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/skammer/32/143_2.png) [@Skammer](https://boards.straightdope.com/u/Skammer)\ **Post date:** [October 11, 2013, 8:45pm UTC](https://boards.straightdope.com/t/excel-formula-help/670118/27 "2013-10-11T20:45:07Z")
I GOT IT!! **Troutman** ’s array formula above was tantalizingly close. He had suggested: {=SUM(IF(FREQUENCY(IF($C$1:$C$10=1,IF($B$1:$B$10=D20,$A$1:$A$10)),$A$1:$A$10),1))} What finally worked after a week of on-and-off hitting my head against a wall was: {=SUM(IF(FREQUENCY(IF($C:$C=1,IF($B:$B=D2,$A:$A)),$A:$A) **\>0** ,1))} Woot!! Perfect timing, because my vacation starts in 75 minutes and now I won’t have to think about this all week. Thanks for all your help everyone! ---
**Author:** ![Amateur\_Barbarian](https://avatars.discourse-cdn.com/v4/letter/a/59ef9b/32.png) [@Amateur\_Barbarian](https://boards.straightdope.com/u/Amateur_Barbarian)\ **Post date:** [October 11, 2013, 8:58pm UTC](https://boards.straightdope.com/t/excel-formula-help/670118/28 "2013-10-11T20:58:27Z")
A-mazin’. I would have bet it couldn’t be done. Gratz! [Previous page](https://boards.straightdope.com/t/excel-formula-help/670118.md?page=1)