Hi everyone.
I am creating a spreadsheet to help me forecast time spent in projects.
I'm sure there must be a simpler formula to do what I need.
For every month, I want to count how many times a Project Name is being looked at. And then I want to multiply the number by the rate, which is different for each person.
So in the example below, Person 1 is spending Jan on Project A. This should be 3. Person 2 is also 3 in Project A, Person 2 is on Project A but their rate is 4.
You can see in the formula for Project A in C13, I've had to do a count for every person. This is the bit I think could be simpler, but I just don't know how? Is there a way?
I am creating a spreadsheet to help me forecast time spent in projects.
I'm sure there must be a simpler formula to do what I need.
For every month, I want to count how many times a Project Name is being looked at. And then I want to multiply the number by the rate, which is different for each person.
So in the example below, Person 1 is spending Jan on Project A. This should be 3. Person 2 is also 3 in Project A, Person 2 is on Project A but their rate is 4.
You can see in the formula for Project A in C13, I've had to do a count for every person. This is the bit I think could be simpler, but I just don't know how? Is there a way?
Book1 | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Person | Rate | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | ||
2 | Person 1 | 3 | Project A | |||||||||||||
3 | Person 2 | 3 | Project A | |||||||||||||
4 | Person 3 | 4 | Project A | |||||||||||||
5 | Person 4 | 3.5 | Project B | |||||||||||||
6 | Person 5 | 2 | Project B | |||||||||||||
7 | Person 6 | 4 | Project A | |||||||||||||
8 | Person 7 | 2 | Project C | |||||||||||||
9 | Person 8 | 2 | Project C | |||||||||||||
10 | Person 9 | 3 | Project A | |||||||||||||
11 | Person 10 | 3 | Project B | |||||||||||||
12 | ||||||||||||||||
13 | Project A | 17 | ||||||||||||||
14 | Project B | 8.5 | ||||||||||||||
15 | Project C | 4 | ||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C13:C15 | C13 | =COUNTIF(C2,A13)*B2+COUNTIF(C3,A13)*B3+COUNTIF(C4,A13)*B4+COUNTIF(C5,A13)*B5+COUNTIF(C6,A13)*B6+COUNTIF(C7,A13)*B7+COUNTIF(C8,A13)*B8+COUNTIF(C9,A13)*B9+COUNTIF(C10,A13)*B10+COUNTIF(C11,A13)*B11 |