Struggling with COUNTIFS/COUNTIF with multiple criteria

[craigs85]

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?

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

 

[Fluff]

How about

=SUMIFS($B$2:$B$11,$C$2:$C$11,A13)

 

[craigs85]

I think that looks like its done what I need.
I was thinking about it all wrong wasn't it!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[craigs85]

Ah, I found my first issue. And apologies, my example above was a very basic version of what I would like to achieve. I thought a solution to that might help me with my more detailed sheet.

So, rather than at a month level, I am breaking it down into weeks, and want to know the total for the month.

So in this example below, It returns #VALUE using the suggestion as I am now expanding the criteria range. Is there a nice way of doing this?

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Month 1				Month 2				Month 3
2	Person	Rate	Week 1	Week 2	Week 3	Week 4	Week 1	Week 2	Week 3	Week 4	Week 1	Week 2	Week 3	Week 4
3	Person 1	3	Project A	Project A
4	Person 2	3	Project A	Project A
5	Person 3	4	Project A	Project B
6	Person 4	3.5	Project B
7	Person 5	2	Project B
8	Person 6	4	Project A
9	Person 7	2	Project C
10	Person 8	2	Project C
11	Person 9	3	Project A
12	Person 10	3	Project B
13
14			Month 1	Month 2
15	Project A		#VALUE!
16	Project B		8.5
17	Project C		4
Sheet1

Cell Formulas
Range	Formula
C15	C15	=SUMIFS($B$3:$B$12,$C$3:$D$12,A15)
C16	C16	=COUNTIF(C4,A16)*B4+COUNTIF(C5,A16)*B5+COUNTIF(C6,A16)*B6+COUNTIF(C7,A16)*B7+COUNTIF(C8,A16)*B8+COUNTIF(C9,A16)*B9+COUNTIF(C10,A16)*B10+COUNTIF(C11,A16)*B11+COUNTIF(C12,A16)*B12+COUNTIF(C14,A16)*B14
C17	C17	=COUNTIF(C5,A17)*B5+COUNTIF(C6,A17)*B6+COUNTIF(C7,A17)*B7+COUNTIF(C8,A17)*B8+COUNTIF(C9,A17)*B9+COUNTIF(C10,A17)*B10+COUNTIF(C11,A17)*B11+COUNTIF(C12,A17)*B12+COUNTIF(C14,A17)*B14+COUNTIF(C15,A17)*B15

 

[jasonb75]

You need sumproduct for that

=SUMPRODUCT($B$3:$B$12*($C$3:$D$12=A15))

With SUMIFS, the sum range and criteria range must be of equal size, you can't have a single column in 1 and 2 columns in the other.

 

[craigs85]

Thank you. Will give that a try! ?

 

[craigs85]

Thank you both @Fluff and @jasonb75
Excellent and super quick answers and have given me what I need!