Need a SUMPRODUCT/SUMIFS/IDEX/MATCH maybe array formula. Please help!!

[TheClaw]

Hi there,

I am using excel 2013 and am really struggling to come up with a solution to the following problem:

I look after a spreadsheet that keeps track of the mandays of a collection of projects by week and is laid out thus:

Month: Feb Feb Feb Mar Mar Jun Jun Jun Jul Jul
Project Name

Project 1 10 20 10 25 15 10 20 10 20 15
Project 1 10 20 10 25 15 10 20 10 20 15
Project 2 10 20 10 25 15 10 20 10 20 15
Project 2 10 20 10 25 15 10 20 10 20 15


I want to sum the total value of all the cells that fall at the intersection of Project 1 and Feb (I've obviously anonymised the data here). The formula needs to array all the manday cells so that if I add another line for Project 1 and add more mandays, it will automatically sum them to the total.

Here's the bit that makes it slightly trickier though....

Mixed in with the manday data there are cells that contain text, so it looks more like the following:

Month: Feb Feb Feb Mar Mar Jun Jun Jun Jul Jul
Project Name

Project 1 10 20 10 25 15 10 A 10 20 15
Project 1 W 20 P 25 15 B 20 10 20 15
Project 2 10 20 10 25 15 10 20 10 20 O
Project 2 10 D 10 25 15 R 20 10 20 15


I've played around with different formulas with a whole load of different combinations of SUMIF/SUMIFS/SUMPRODUCT/INDEX/MATCH and array formulas, which baffled me. The two main problems I've encountered are formulas returning a #VALUE error because of the text cells included in the array and INDEX & MATCH formulas only summing the first match returned.

Any help on this would be sincerely and greatly appreciated - please let me know if any further information is required.

Cheers,

Chris

EDIT: So I'm new to this forum and I just realized it's changed the format of the examples when I posted. The projects are down column A and the months are in row 1. The mandays are at the intersection between the two.

 

[yesterdays]

	A	B	C	D	E	F	G	H	I	J	K
1	Month:	Feb	Feb	Feb	Mar	Mar	Jun	Jun	Jun	Jul	Jul
2	Project Name
3
4	Project1	10	20	10	25	15	10	A	10	20	15
5	Project1	W	20	P	25	15	B	20	10	20	15
6	Project2	10	20	10	25	15	10	20	10	20	O
7	Project2	10	D	10	25	15	R	20	10	20	15
8
9
10		Project:	Project1
11		Month:	Feb
12		Sum:	60

<colgroup><col span="11"></colgroup><tbody>
</tbody>

C12 : =SUM(SQRT((IFERROR(($B$1:$K$1=$C$11)*$B$4:$K$7,0)*IFERROR(($A$4:$A$7=$C$10)*$B$4:$K$7,0))))
Ctrl + Shift + Enter
Hope this help

 

[TheClaw]

Thanks very much yes that almost works perfectly. It wasn't quite summing them all up correctly and I couldn't quite understand why, after some messing about I realized that it neglects to sum up any numbers in the last 4 rows of the project name it's looking for. I haven't a clue why? But it's not a problem, I just added some additional rows for each project name and hid them.

Thanks again!

 

[yesterdays]

Would you mind post your data layout?
and the formula you have edited to suit your need, too?
Leave things incomplete when it could be is not my type

 

[Aladin Akyurek]

@TheClaw

What is the value for Project 1 in Feb? 60?