Can I get criteria from columns to populate down?

C

Charles Stover

I would like to have results populate the cells below each heading.
For example, instead of manually putting the names (Too Many), I would like
it to auto populate the "Assigned To" going down. All the info would be
pulled from existing columns in the spreadsheet.

Ticket Count by Age(Days)
| Assigned To | <=2 | 3 to 7 |>=8 | Total | % |
 
G

GSnyder

Hi, Charles.

I'm not 100% sure I understand the question, but I'll give it a shot. Are
folks entering data on the sheet and you want it to automatically create some
formulas or do you already have all of the data and you just want to recreate
some additional information from it. In other words, do you have Last name
in one column and First name in another and you just want to put them
together, or is it more complicated than that?
 
S

Shane Devenshire

Hi,

Its not clear on what you want. Why don't you want to show us a sample.
 
C

Charles Stover

I apologize.

I have a spreadsheet with alot of populated columns.
1 column has "Assigned to" names in it.

| Assigned to |
AV HDSUPPORT T2
Jason Quattrone
AV HDSUPPORT T1
Leonard Bolton
Andrey Lubimtsev
Daniel Giampietro
Jason Applebaum

I would like to be able to auto populate the names with no dups in another
column.

I have another column with days in it (1,2,4,7,34,etc.) This reflects how
many days each person above has been working on an issue.

| AGE |
5
1
6
3
7
18
7
11
7

I would like to populate the columns to the right of the new column above
with the total of tickets for that particular person.

EX:

New Columns= | Assigned To | <=2 | 3 to 7 |>=8 | Total | % |
Auto Populate= |Jason Applebaum| 3 | 2 | 6 | 11 | 25% |

I would need this to populate down repeating this for all the names without
any duplicates.

I hope this is a better explanation.

Thanks.
Charles
 
S

Shane Devenshire

Hi,

Here is a start:

1. The easiest way to get a list of unique entries is to us the Data,
Filter, Advanced Filter with Unique Records checked.
2. Your first column of formula after the names would be

=IF(D2="","",SUMPRODUCT(--($D2=$A$2:$A$15),--($B$2:$B$15<=2)))

This formula assume the unique names are in column D starting at row 2. The
full list of names in in column A and the Age is in column B.

2nd formula:

=SUMPRODUCT(--($D2=$A$2:$A$15),--($B$2:$B$15>=3),--($B$2:$B$15<=7))

3rd formula:

=SUMPRODUCT(--($D2=$A$2:$A$15),--($B$2:$B$15>=8))
 
Top