CountIf with preceding cell as criteria?

[elliott.smith]

Hello Excel Masters!


I'm working on a guest list for an event we recently had, and I'm trying to figure out how many guests each registered attendant had. However, since many registered attendants brought multiple guests, I have multiple lines of data for the registered attendant.


Column C is the last name of the guest, Column D is the first name of the guest, and column E is the registered attendant's full name. If Suzie Q registered attendant brings 3 guests, there are three rows of data for which column E reads "Suzie Q".


Here's what I'm looking to do:

- Count how many registered attendants brought 1 guest, 2 guest, and 3 or more guests.


I'm not sure if I can use =CountIf and set the criteria to be the cell above or not, but I haven't found anything concrete in forums. If I say =CountIf(E4:E437..., what does the criteria need to be?


If formulas are not the best way to do this, are Pivot Tables? I'm not proficient enough for macros, so any help would be much appreciated!


Elliott

 

[Luke M]

A PivotTable would be the fastest. With the data columns you described, you would put the Registered Attendant as a Row label and as a Data item (count of...). You now have a quick list showing all the attendants and how many guests they brought.

 

[elliott.smith]

Thanks Luke- that was a big help!


Now I'm hitting another (probably very simple) wall.


I want to represent this in graph form. Specifically, on a pie chart, I'm trying to show the breakdown for how many registered attendants brought 1 guest, how many brought 2, and how many brought 3 or more. The format of the output for the pivot table gives me one column with all the registered attendants' names and another column with the number of guests they have.


Since there are 268 registered attendants in column A, Excel won't let me switch columns and rows (there's a cap at 255 columns). Any ideas how to manipulate the pivot table to provide the pie chart breakdown I'm trying to make? Been switching things around for hours and I'm getting nowhere!


Thank you so much for your help!

 

[Sajan]

Hello Elliott,


Here is a formula based approach to get the count of those who brought 1 guest:


=SUMPRODUCT(N((MATCH(RegP,RegP,0)=ROW(OFFSET(A$1,,,ROWS(RegP))))*COUNTIF(RegP,RegP)=1))


Here RegP refers to the range of registered persons such as E1:E100


Replace the "=1" with "=2" to get the count of those who brought two guests, and ">2" to get the count for those who brought more than two guests.


You should be able to then chart the above results.


Cheers,

Sajan.