Excel formula help

Factual Questions
1

Can anyone help me figure out an excel formula to do the following?

I have a table with thousands of rows with data like this:



     A       B      C
 1|   23 | May 13 | 1
 2|  197 | May 13 | 0
 3|  197 | Apr 13 | 1
 4| 1201 | Jun 13 | 1
 5| 2490 | Jun 13 | 0
 6| 2490 | Apr 13 | 1
 7| 6003 | Jul 13 | 0
 8| 9543 | May 13 | 0
 9| 9543 | May 13 | 1
10| 9543 | Jul 13 | 1

I need a formula that for each month in column B, counts the number of unique values in column A that have a ‘1’ in column C.

So using the sample data above my output would look like this:



   D   | E
Apr 13 | 1
May 13 | 2
Jun 13 | 1
Jul 13 | 0

Any ideas?

2

I’m not sure how to do it in Excel, although I’m sure there probably is a way to do it with an Excel formula.

Any chance you could use a database instead? This feels like a job for a database, not a spreadsheet. You could do this in 2 seconds with SQL. Is this a one-off kind of thing, or something you’re going to need to maintain or build upon?

3

I must be misunderstanding what you’re asking. It looks like there are two unique values in Column A for April that have a “1” in Column C, but your output shows “1” for April.

4

This is just one part of a large Excel workbook with data that will be updated monthly. This particular function would probably be easier in a database but I have to figure out a way to make it work within Excel because that’s where everything else is.

Gary - you’re right, I miscounted. That’s why I need a formula! :smiley:

5

I agree that it’s really not a problem best suited to Excel. It will take several pages of cascading formulae to condense the pseudo-query to totals, and every step will be prone to logical errors. I can think of a couple of reasons why it might work on one month’s stats and inexplicably choke on another (at least, until you refined it further, trapping the exceptions), and you’d have no confidence without hand-checking the results.

In a database, it’d be about a 3-line query and you could trust it.

In the words of Lucius Fox, “Good luck!” :frowning:

6

This is a problem that seems perfectly suitable for an Excel Pivot Table.

7

That was what I was thinking, but I haven’t worked with pivot tables in so long that I don’t know for sure.

8

Mmm. That might simplify some of the evaluation but in the end, finding a sum of only unique values in a list is going to be a bitch. Excel just doesn’t have a very good set of tools for that kind of evaluation.

ETA: I’m not sure there is any way to do it in native Excel formulae; it’s going to need VBA for some of the conditional sorting, comparison and counting.

9

Can you sort the data?

If you can, sort first by Column C, then B, then A. Either toss everything in Col C with a 0 or ignore it in the formula.

Create Col D:

The logic goes like this:
If Col C is a 0, make Col D = 0.
If ColB and ColA in this row are different than the previous row, make Col D = 1.
Otherwise, make Col D = 0.

=IF(c2=0,0,IF(b2<>b1,1,IF(a2<>a1,1,0)))

Then create a pivot table to add up the values in Col D for each month.

If you need help with the pivot table let me know.

10

Couldn’t it be done in several steps?

I’ve gotten some good formulas from xl-logic.com before, but the site appears to be defunct now.

11

Yup.

I’d first create Column D, which just finds out what month each entry is. Enter “=month(b2)” into D2, and drag it down.

(Also, I’m naming your columns for clarity. A=“Serial”, B=“Date”, C=“0/1”, D=“Month”.)

So create a pivot chart:

Highlight the columns.
Go to “Insert” -> “PivotTable”.
Click “Ok”.

Then drag “Month” into the “Row Labels” box., then put “0/1” under that.
Drag “Serial” into the “Values” box.

That will give you something that looks like this:


Row Labels     Count of Serial
4
    1                 2
5
    0                 2
    1                 1
6  
    0                 1
    1                 2
7
    0                 1

If you want to clean up the month column to be text rather than a number, you can switch out “=MONTH(B2)” for “=if(MONTH(B2)=1, “January”, if(MONTH(B2)=2, “February”, if(MONTH(B2)=3, “March”, if(MONTH(B2)=4, “April”, if(MONTH(B2)=5, “May”, if(MONTH(B2)=6, “June”, if(MONTH(B2)=7, “July”, if(MONTH(B2)=8, “August”, if(MONTH(B2)=9, “September”, if(MONTH(B2)=10, “October”, if(MONTH(B2)=11, “November”, if(MONTH(B2)=12, “December”, “”))))))))))))” That will make it look like:


Row Labels     Count of Serial
April
    1                 2
May
    0                 2
    1                 1
June
    0                 1
    1                 2
July
    0                 1
12

It’s pretty straightforward with a pivot table. I did it really quick with your sample data, here’s a screenshot:

The report filter is column C = 1.

13

Hmm, I could have the data sorted before I bring it in. I think you might have pointed me to a solution here.

Instead of a pivot table, I could probably use the COUNTIF function to count values in column D if column B matches the month I want. I’ll play around with it.

ETA:
mbetter’s solution looks even better, although I have to admit my experience with pivot tables is rusty.

14

The problem with my solution is that if you have two identical rows, they will both be counted in the final output. Without knowing what your data represents, I don’t know if this is even a possibility. I can’t find an easy workaround at the moment.

15

Yep; I built the pivot table as you did but it’s counting all the values in A that meet the conditions in B and C. I need to count the unique values.

16

I am merely a good journeyman user of Excel, but I don’t know any way to do this kind of serial evaluation. I will be interested to see the solution if there is one. (I still think you’re going to have to write some VBA code for the conditional looping. The conditional control in Excel is pretty primitive by even BASIC standards.)

17

So with my pivot table at a dead end (cannot ignore duplicate records), I played around with cmosdes’ suggestion to sort the data first. The problem is, the values in column C are not constant - the user can select certain options on the display page which determine the values in that column.

I could, perhaps, attach a macro to each of the user interface controls which would re-sort this set of data each time. But that would be… burdensome.

18

Okay, I’ve been thinking about this a bit, and I think my favorite solution so far is to first make a “shadow copy” of the data sheet, where you have another sheet laid out like the source sheet that consists of just references to the source data sheet. Then, on that sheet, add a new column that concatenates fields B and C. This will now be your second level of row labels in the pivot table.

19

Skammer - am I correct in thinking that when there are duplicate numbers with different months, you want to count it under the first month but not the second? So in your example, the value 9543 appears for both May and July, but you are counting it only in May?

20

Without the answer to Troutman’s query being posted this may be a furphy.
Also I’m guessing that we are looking for a solution, not an elegant solution.

Pivot tables (note plural) will do this.

I’m assuming column B is an Excel date.

Create the Pivot table with Col C =1 as a filter, Column A as the single filed in the column section and the rows section having column B in the pivot table twice, once with the value as minumin and once as count.

Which will give you something like this:

Col C 1

Values

Col A Min of Col B Count of Col B2
23 1/05/13 1
197 1/04/13 1
1201 1/06/13 1
2490 1/04/13 1
9543 1/05/13 2
Grand Total 1/04/13 6
Now either convert the pivot table into values, or use simple formula to put the values of COLs A-C into say COLs E-G. Remember to put appropriate headings as below:

COL E COL F COL G
ID Month Count
23 1/05/13 1
197 1/04/13 1
1201 1/06/13 1
2490 1/04/13 1
9543 1/05/13 2

Run a pivot table on the transformed data in COLs E-G (or if you prefer use countifs)
Again set the filter as being COL G (or Count) =1
Set the Month as the single month in the coulmns section and put ID into the row section as a count.

Count 1

Count of ID
Month Total
1/04/13 2
1/05/13 1
1/06/13 1
Grand Total 4