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.

EDIT: So I’m new to this forum and have just realised that posting has changed the format of the examples I gave above. The project names are in column A and the months are in row 1, the mandays are at intersections between the two.

Cheers,

Chris

Try this Array Formula...
Sum Array.xls

Remember, since this is an Array Formulas, you must use Ctrl-Shift-Enter (then the magical curly brackets appear)...

Let us know if you have any questions...

This option can also nice.

There are currently 1 users browsing this thread. (0 members and 1 guests)