Forum Discussion
Formula help, counting related
| Date | Cat | Customer | SP1 | SP2 | Stk # |
| 5/31 | 6/24 | Baker | NM | DH | L6456 |
| 5/31 | 6/26 | Northrup | DH | L6314 | |
| 5/31 | 6/26 | Rayner | MD | MVC | L6316 |
| 5/31 | 6/16 | Sangalis | DG | L6441 | |
| 5/31 | G | Theiss | MVC | L6233 | |
| 5/31 | G | Thibodeaux | NM | L6298 | |
| 5/31 | G | Varghese | JK | L6277 | |
| 6/1 | G | Billot | DG | 32041 | |
| 6/1 | G | Brain | DA | L4872 | |
| 6/1 | G | Dizon | DH | L6116 | |
| 6/1 | G | Eagleton | DG | L5576 | |
| 6/1 | G | Lyons | SB | 32079 | |
| 6/1 | G | Meehan | MD | 32075 | |
| 6/2 | G | Floyd | JP | L5778 | |
| 6/2 | G | Martin | DT | L6268 | |
| 6/2 | G | Moody | NS | L6266 | |
| 6/2 | G | Neal | NM | L6265 | |
| 6/2 | G | Pennington | BWB | L5784 | |
| 6/2 | G | Reiner | DG | 32080 | |
| 6/2 | G | Tadlock | DA | 31979 | |
| 6/2 | G | Young | JP | L6245 | |
| 6/3 | G | Abrahams | NM | 32065 | |
| 6/3 | G | Cooper | MD | 32082 | |
| 6/3 | G | FDS Interiors | BC | L6232 | |
| 6/3 | G | Gallaher | JK | L6246 | |
| 6/3 | G | Guillory | JK | L5659 | |
| 6/3 | G | Hodges | JP | L5762 | |
| 6/3 | G | Murphy | DT | L6191 |
Need to count the number of "deals" for each salesperson.
- If SP2 column is blank, then add 1 for SP1
- If SP2 has a salesperson, then add .5 for SP1 and .5 for SP2
- Results will be total for each unique value (salesperson).
Thank you.
1 Reply
Hi Lance,
If
- nothing depends on what is in SP1 ccolumn;
- you need only totals for each SP;
- totals are for entire table (not by time periods, category, etc)
you may calculate count of blank cells =COUNTBLANK() and non-blank cells =COUNTA() in SP2 column and multiply them accordingly on 1 and 0.5 for SP1, and on 0 and 0.5 for SP2.
The only point be sure empty cells in SP2 column are really empty, i.e. without spaces and special symbols. If doubt check with =ISBLANK(<cell>).
