PDA

View Full Version : [SOLVED] Counting data occurances between two non-adjacent columns



bdr127
11-16-2004, 05:34 PM
Hi there... my first time at VBAExpress.com. I'm hoping someone might be able to help me out with this.

I have four columns: Name, Job, Company, and Confirmed. The first three are strings; Confirmed just shows an "X" if the data has been confirmed.

Example: (I apologize for the formatting.)

Name Job Company Confirmed
Bob Banker National X
Bill Engineer Coastal
Brent Clerk National X
Brandon Clerk Coastal X
Brad Engineer Coastal
Ben Banker National X
Bart Engineer Coastal X


I want to calculate the number of confirmed entries for each type of job. In the above data, Banker=2, Clerk=2, Engineer=1. I want to put the results in another sheet with columns Job and Number Confirmed.

It seems simple enough... Although I've been away from Excel for a while, I just can't seem to figure out how to count data from one column versus another column with columns that are not adjacent. It's almost like I'm looking to do a JOIN in SQL..... Anyone have an idea of how to help me out with this situation? Thanks in advance.

--BDR

Zack Barresse
11-16-2004, 05:59 PM
Hi,

You can use formulas for that. If you'd prefer VBA, this can be done too, but I always figure why reinvent the wheel. :dunno


=SUMPRODUCT(--($B$2:$B$8=F2),--($D$2:$D$8="X"))


An example workbook is attached.


HTH

bdr127
11-16-2004, 06:18 PM
Thank you so much! Wow, that really was simple, huh?

FinancialAnalystKid
11-17-2004, 08:13 AM
Hi - first time here on VBAExpress!!

I was wondering about this formula. What do the dashs '--' do in a formula as shown in the above example?

Zack Barresse
11-17-2004, 10:22 AM
Then welcome to the board!!!


The two minus signs are used for coercion effects. It would be the same thing is you used +0 or *1. It's best described in an array-type formula (as SUMPRODUCT naturally acts).

If you take an array of values and check it against something it will return a TRUE/FALSE value. Either they match or they don't. We then want to Count all of these occurances, but need to coerce the Boolean (True/False) value into a Binary (1/0) value for that, which is where the double negation comes into play. Let's look at the following formula ..

=SUMPRODUCT(--(A1:A5="YUPPER"))

If we have in col A ...



A1 NOPE
A2 YUPPER
A3 YUPPER
A4 NOPE
A5 YUPPER


As you can see, there would be three correct answers that match. This is done like the following..

=SUMPRODUCT(--(A1:A5="YUPPER"))

...

=SUMPRODUCT(--({"NOPE","YUPPER","YUPPER","NOPE","YUPPER"}="YUPPER"))

..

=SUMPRODUCT(--({FALSE,TRUE,TRUE,FALSE,TRUE}="YUPPER"))

..

=SUMPRODUCT({0,1,1,0,1})


We can do this because of Excel's ability to say that a True Boolean value is equivelant to a Binary 1, and a False Boolean value is equivelant to a Binary 0 .

You can set this up to more than one condition, which will check rows against rows and columns against columns (meaning it is not cumulative across arrays). So in the case of the formula ...

=SUMPRODUCT(--($B$2:$B$8=F2),--($D$2:$D$8="X"))

.. we just added another array (seperated by a comma in the syntax of SUMPRODUCT) and checked column B to see if it matched the value in F2, we matched (or aligned) that array against another that checked column D for an X. Those cells that had a match in each corresponding row for each condition received another 1 in the count.

Please note, we did use the comma in this case where we could have also used the * symbol. We don't need to here because of how SUMPRODUCT works, it does the array multiplication naturally; the help files describe this process well (at least in 2002). The equivelant Logical operation that * produces is AND. To produce an OR type of logical expression, the + sign can be substituted. For an example take the formula version ...


=SUMPRODUCT(--($B$2:$B$8=F2)+($D$2:$D$8="X"))

We make sure and take out the comma and leave both conditions inside of one array as we can take one or the other. In our first example of the YUPPER cells, we'll add another condition...

In col A ...



A1 NOPE
A2 YUPPER
A3 YUPPER
A4 NOPE
A5 YUPPER


In col B ...



B1 MAYBE
B2 NOPE
B3 NOPE
B4 NOPE
B5 MAYBE


We'll count all occurances of YUPPER in col A that match MAYBE in col B.


=SUMPRODUCT(--(A1:A5="YUPPER"),--(B1:B5="MAYBE"))

...

=SUMPRODUCT(--({"NOPE","YUPPER","YUPPER","NOPE","YUPPER"}="YUPPER"),--({"MAYBE","NOPE","NOPE","NOPE","MAYBE"}))

..

=SUMPRODUCT(--({FALSE,TRUE,TRUE,FALSE,TRUE}="YUPPER"),--(TRUE,FALSE,FALSE,FALSE,TRUE}))

..

=SUMPRODUCT({0,1,1,0,1},{1,0,0,0,1})

..

{snip}
We take these two arrays and line them up, a visual like so ..
{0,1,1,0,1}
{1,0,0,0,1}
multiply them down
{0*1=0,1*0=0,1*0=0,0*0=0,1*1=1}
{0,0,0,0,1}
{/snip}

=SUMPRODUCT({0,0,0,0,1})

..

1

Now to count all occurances of YUPPER in col A AND all occurances of MAYBE in col B ...

=SUMPRODUCT(--(A1:A5="YUPPER")+(B1:B5="MAYBE"))

I leave the formula audit of this one to you ...

Zack Barresse
11-17-2004, 05:27 PM
And I would be hard pressed if I didn't provide a link to Aladin Akyurek's explanation of this ...


http://www.mrexcel.com/wwwboard/messages/8961.html


His is far superior to mine and well worth the read. :yes