Sum an array based on a row criteria?

[Westie102]

Hi everyone,

I hope I explain this well, I'm certain many of you will find this soooo easy...., but I need a bit of guidance, please

I have a spreadsheet similar to the one below. Effectively I want to be able to add up the sum of all of the 'level 1' rows in that range (marked in red) and then separately, all of the level 2's (blue) and then 3's and 4's.


[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Level[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date3[/TD]
[TD]Date 4[/TD]
[TD]Date 5[/TD]
[TD]Date 6[/TD]
[TD]Date 7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4[/TD]
[TD]
[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]
[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]
[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]
[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]














I think I have to use INDEX and MATCH, but I just can't work out how to get it to add up across the columns.... please point me in the right direction

Thanks in anticipation

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Level	Date 1	Date 2	Date3	Date 4	Date 5	Date 6	Date 7
2	1	3			4		3			38
3	2	2	2			9				34
4	4	2		20	20					7
5	1		2			7		7		48
6	3			1		2		4
7	1	1	6				5
8	4			3	3
9	2	6			6	7		2
Sheet1

Cell Formulas
Range	Formula
J2	=SUMPRODUCT(($A$2:$A$9=ROW($A1))*($B$2:$H$9))

 

[Westie102]

Oh yes!

I just need to do a little more i think so I can 'pick and choose the level..... but awesome!!!!!!

I might be back, but I'll try to take it from here.... thank-you sooo much!!

very happy indeed.

 

[Fluff]

You're welcome & thanks for the feedback.
If you want to stipulate the level, you could put it in I2 & use
=SUMPRODUCT(($A$2:$A$9=I2)*($B$2:$H$9))

 

[Westie102]

Perhaps not - can you help with the next bit (sorry!)..... I want to be able to specify the level selected (so in $A$1, for example, I type "4" and it gives the result "48", type "3" and it results "7"?

Thanks so much again....

I know I should be able to work this out, just pressured against a deadline and can't for the life of me, work it out!

 

[Westie102]

LOL! You must have read my mind! THANK-YOU!

If I knew who you were, I'd put you on my Christmas card list right now! Thank-you, thank-you, thank-you!

 

[Fluff]

Glad to help & thanks for the feedback