excel help...

[Fullmetal Chocobo]

In the given table....

A.........................B.....................C.....................D................E............F
sterry..................jones..............daniela................2.............909...........
butler..................bexar..............christine..............1.............906...........
blanco.................sterry..............jay.....................2.............903...........x
san jac................butler..............sara...................1..............906..........
theater................tower..............joseph................2...............906........
jackson...............sterry..............mary..................1..............903.........
upd....................butler...............jason..................2.............906.........x
butler.................san jac..............elvis..................1..............903.......x

I am trying to figure out a way to total each number in E-column have a mark in F-column next to it.
So in the table above, the results would be:
903 = 2
906 = 1
909 = 0
etc....

 

[KLin]

SELECT A5, Count(A6) from Sheet1 where A6 = 'x' Group by A5
See how easy it is if that data was in Microsoft access? 😉

The only way I know how to do it would sort the columns by A5, then have a field for each distinct number within the CountIF (ie 903 is the first number, CountIf(F1:F3, "x").


EDIT: FYI columns are alpha only in Excel, so my example should really be like that.

 

[Spydermag68]

I do not understand logic behind what you want to do.
What is the reasoning for:
903 = 2
906 = 1
909 = 0

 

[KLin]

Originally posted by: Spydermag68
I do not understand logic behind what you want to do.
What is the reasoning for:
903 = 2
906 = 1
909 = 0

He wants a count of each 3 digit number that is marked with an X I believe.

 

[Fullmetal Chocobo]

Okay, 909, 903, etc are badge numbers for people that give security escorts around campus. The "x" in F-column signifies a self-initiated escort--an escort that was not dispatched to them. We are trying to find a way to total the number of self-initiated escorts for each individual.

 

[KLin]

Originally posted by: Fullmetal Chocobo
Okay, 909, 903, etc are badge numbers for people that give security escorts around campus. The "x" in F-column signifies a self-initiated escort--an escort that was not dispatched to them. We are trying to find a way to total the number of self-initiated escorts for each individual.

Your data really should reside in access or some other database program (mySQL for instance). This would be a piece of cake without having to rely on Excel functions.

 

[mayest]

Originally posted by: Fullmetal Chocobo
Okay, 909, 903, etc are badge numbers for people that give security escorts around campus. The "x" in F-column signifies a self-initiated escort--an escort that was not dispatched to them. We are trying to find a way to total the number of self-initiated escorts for each individual.

I still don't quite understand your data, but the solution is quite simple and you do not need Access. If you are trying to count how many x's each badge number has (903 has 2 x's in the list, 906 has 1, and 909 has 0) then do this:

1) Create a list of badge numbers, each listed only once (as you did when showing the results you expect).
2) Put this formula next to each badge number: =SUM(($F$1:$F$8="x")*($E$1:$E$8=E10)*1)

That formula is an array formula so you need to press CTRL+Shift+Enter to enter it properly. I assume that your data starts in row 1 and goes to row 8, so you will have to adjust those ranges. Also, the reference to E10 refers to the first badge number listed. When you copy the formula down, it will change to E11 (second badge number), E12 (third), and so on.

Let me know if this works or not.

 

[Fullmetal Chocobo]

Originally posted by: mayest

Originally posted by: Fullmetal Chocobo
Okay, 909, 903, etc are badge numbers for people that give security escorts around campus. The "x" in F-column signifies a self-initiated escort--an escort that was not dispatched to them. We are trying to find a way to total the number of self-initiated escorts for each individual.

I still don't quite understand your data, but the solution is quite simple and you do not need Access. If you are trying to count how many x's each badge number has (903 has 2 x's in the list, 906 has 1, and 909 has 0) then do this:

1) Create a list of badge numbers, each listed only once (as you did when showing the results you expect).
2) Put this formula next to each badge number: =SUM(($F$1:$F$8="x")*($E$1:$E$8=E10)*1)

That formula is an array formula so you need to press CTRL+Shift+Enter to enter it properly. I assume that your data starts in row 1 and goes to row 8, so you will have to adjust those ranges. Also, the reference to E10 refers to the first badge number listed. When you copy the formula down, it will change to E11 (second badge number), E12 (third), and so on.

Let me know if this works or not.

=SUM((Jan!K:K="x")*(Jan!J:J=B12)*1) is the formula that I used. I am doing the calculations on a sheet titled "Totals", and it is referencing a sheet titled "Jan"; K:K refers to the entire column of K where the "x" indicates self-initiated escorts; B on the current sheet contains the bade number of the individual...

Using that formula and setup, the only number present is 0. I'm not quite sure what is meant in that the formula is an array, and the use of "enter" in the cell be used. So that may be the problem.

 

[mayest]

An array formula cannot be entered by pressing the Enter key like a normal formula. After you type it into the formula bar, press Ctrl+Shift+Enter.

Array formulas will not work with full-column references. If you must use a full column, change your references to Jan!K1:K65535 and Jan!J1:J65535 (you have to stop one row short of the entire column).

If you really have that much data, then I'll agree that you should be using Access (or Excel 2007). If you don't, then just use what you need, plus enough extra rows.

 

[Fullmetal Chocobo]

Originally posted by: mayest
An array formula cannot be entered by pressing the Enter key like a normal formula. After you type it into the formula bar, press Ctrl+Shift+Enter.

Array formulas will not work with full-column references. If you must use a full column, change your references to Jan!K1:K65535 and Jan!J1:J65535 (you have to stop one row short of the entire column).

If you really have that much data, then I'll agree that you should be using Access (or Excel 2007). If you don't, then just use what you need, plus enough extra rows.

Excellent. It worked great sans a few little issues. But it is working... I need to tweak the values, as it is increasing the array dimensions as I copy it down the column (range goes from 1:15000 to 2:150001, etc), but that is easily fixable. Thank you very much for your help.

 

[NeoV]

the solution here is a pivot table, end of discussion.

Array formulas are nice and all, but overkill for this basic fuction that a pivotal table is basically made for.