Count occurrences of a value based on condtions in both the column and row

mcs9b

New Member
Joined
Mar 1, 2010
Messages
4
I have a spreadsheet already set up which assigns call (represented by "C") to different people over the entire year. I am looking for a formula that gives an ongoing tally of how many calls each person has based on the day of the week. The spreadsheet looks like this (obvously a very abbreviated version):

A B C D E F G H I J K L M N O

1 JANUARY su mo tu we th fr sa su mo tu we th fr sa.......
2 Jim C C C C
3 John C C C
4 Jane C C C C C
5
6 FEBRUARY su mo tu we th fr sa su mo tu we th fr sa.......
7 Jim C C C C C
8 John C C C
9 Jane C C C
10
11 MARCH su mo tu we th fr sa su mo tu we th fr sa.......
12 Jim C C C
13 John C C C
14 Jane C C


At the bottom of the spreadsheet I have the following table which gives a running tally of calls by day of the week:

su mo tu we th fr sa
20 Jim 2 1 4 2 1 0 2
21 John 3 1 0 1 2 1 1
22 Jane 1 2 2 2 0 3 0

How do I generate these tallies based on the name and day of the week?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
Here's a 2 step process:
Enter your days in Q1:W1 (ie: Su, Mo, etc)
Enter this in Q2 and copy down and across to give you Calls by month:
Code:
=COUNTIFS($B2:$O2,"C",$B$1:$O$1,Q$1)

Now enter this in B20 and copy down and across:
Code:
=SUMIF($A$2:$A$14,$A20,Q$2:Q$14)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,964
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top