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.
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.
Last edited:
