SUMIF not working for multiple coloumns whats the best excel solution?

[SimonVOwen]

Hello All,

I regularly have to deal with Half Hour data spanning a year. So a table 48 by 365.
In this data I need to sum certain dates / times as they have different rates.
Ideally I would want a solution where SUMIF would work across the multiple columns but I am unsure if this can be done or how to proceed.
=SUMIFS(B18:K29,A18:A29,">="&5,B17:K17,">="&5,B17:K17."<="&7)
As a base example of the code I would be trying to run would be like this which returns #value as it cannot cope with B18:K29.
Below is an example of how the data would look and the red section I am trying to sum.
I need to do this for roughly 6/7 areas in the large data setts and they all vary in size.
Any help would be much appreciated.

Regards,

Simon Owen

	1	2	3	4	5	6	7	8	9	10	Half hour Segments
1	0.0387	0.0375	0.0342	0.033	0.0317	0.0322	0.0307	0.0303	0.0313	0.0316
2	0.038	0.0366	0.0312	0.0311	0.0348	0.0306	0.0357	0.0326	0.0307	0.0355
3	0.0403	0.0373	0.0342	0.0337	0.0328	0.0314	0.0313	0.0317	0.0315	0.0321
4	0.0356	0.032	0.0297	0.0292	0.0285	0.0283	0.0277	0.0279	0.029	0.0295
5	0.0344	0.0315	0.0294	0.0284	0.028	0.0275	0.0274	0.0273	0.0285	0.0296
6	0.0348	0.032	0.0296	0.0284	0.0281	0.0279	0.0271	0.027	0.0284	0.0294
7	0.0349	0.0314	0.0294	0.0289	0.0286	0.0277	0.0272	0.0275	0.0287	0.0292
8	0.0335	0.034	0.0302	0.0282	0.0298	0.0283	0.0277	0.0296	0.0281	0.0312
9	0.0355	0.0314	0.03	0.0289	0.0293	0.0283	0.0261	0.0285	0.0275	0.0276
10	0.0352	0.0313	0.029	0.0284	0.0274	0.027	0.0267	0.0272	0.0279	0.0287
11	0.035	0.0314	0.029	0.0285	0.0277	0.0276	0.027	0.0274	0.0284	0.0289
12	0.0342	0.0313	0.029	0.028	0.0274	0.0269	0.0269	0.0269	0.0281	0.0292

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

 

[FormR]

Hi, welcome to the forum!

Here is one possible option you can try:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	5	6	7	8	9	10
2	1	0.0387	0.0375	0.0342	0.033	0.0317	0.0322	0.0307	0.0303	0.0313	0.0316		0.6636
3	2	0.038	0.0366	0.0312	0.0311	0.0348	0.0306	0.0357	0.0326	0.0307	0.0355
4	3	0.0403	0.0373	0.0342	0.0337	0.0328	0.0314	0.0313	0.0317	0.0315	0.0321
5	4	0.0356	0.032	0.0297	0.0292	0.0285	0.0283	0.0277	0.0279	0.029	0.0295
6	5	0.0344	0.0315	0.0294	0.0284	0.028	0.0275	0.0274	0.0273	0.0285	0.0296
7	6	0.0348	0.032	0.0296	0.0284	0.0281	0.0279	0.0271	0.027	0.0284	0.0294
8	7	0.0349	0.0314	0.0294	0.0289	0.0286	0.0277	0.0272	0.0275	0.0287	0.0292
9	8	0.0335	0.034	0.0302	0.0282	0.0298	0.0283	0.0277	0.0296	0.0281	0.0312
10	9	0.0355	0.0314	0.03	0.0289	0.0293	0.0283	0.0261	0.0285	0.0275	0.0276
11	10	0.0352	0.0313	0.029	0.0284	0.0274	0.027	0.0267	0.0272	0.0279	0.0287
12	11	0.035	0.0314	0.029	0.0285	0.0277	0.0276	0.027	0.0274	0.0284	0.0289
13	12	0.0342	0.0313	0.029	0.028	0.0274	0.0269	0.0269	0.0269	0.0281	0.0292
Sheet1

Cell Formulas
Range	Formula
M2	=SUM(INDEX($B$2:$K$13,MATCH(5,$A$2:$A$13,0),MATCH(5,$B$1:$K$1,0)):INDEX($B$2:$K$13,MATCH(12,$A$2:$A$13,0),MATCH(7,$B$1:$K$1,0)))

 

[SimonVOwen]

Hi FormR,

Thanks a lot for the help!! (very much appreciated)
Recently I have been running into the index/match concept a couple of times but wasn't sure how to utilise as hadn't used before.
I have been reading these forums for years now and this was first time I had to actually had to ask a question as usually I could solve my issues from previous posts.

Regards,
Simon

 

[FormR]

Hi, glad it helped - and on reflection we may even get away with this instead:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	5	6	7	8	9	10
2	1	0.0387	0.0375	0.0342	0.033	0.0317	0.0322	0.0307	0.0303	0.0313	0.0316		0.6636
3	2	0.038	0.0366	0.0312	0.0311	0.0348	0.0306	0.0357	0.0326	0.0307	0.0355
4	3	0.0403	0.0373	0.0342	0.0337	0.0328	0.0314	0.0313	0.0317	0.0315	0.0321
5	4	0.0356	0.032	0.0297	0.0292	0.0285	0.0283	0.0277	0.0279	0.029	0.0295
6	5	0.0344	0.0315	0.0294	0.0284	0.028	0.0275	0.0274	0.0273	0.0285	0.0296
7	6	0.0348	0.032	0.0296	0.0284	0.0281	0.0279	0.0271	0.027	0.0284	0.0294
8	7	0.0349	0.0314	0.0294	0.0289	0.0286	0.0277	0.0272	0.0275	0.0287	0.0292
9	8	0.0335	0.034	0.0302	0.0282	0.0298	0.0283	0.0277	0.0296	0.0281	0.0312
10	9	0.0355	0.0314	0.03	0.0289	0.0293	0.0283	0.0261	0.0285	0.0275	0.0276
11	10	0.0352	0.0313	0.029	0.0284	0.0274	0.027	0.0267	0.0272	0.0279	0.0287
12	11	0.035	0.0314	0.029	0.0285	0.0277	0.0276	0.027	0.0274	0.0284	0.0289
13	12	0.0342	0.0313	0.029	0.028	0.0274	0.0269	0.0269	0.0269	0.0281	0.0292
Sheet1

Cell Formulas
Range	Formula
M2	=SUM(INDEX($B$2:$K$13,5,5):INDEX($B$2:$K$13,12,7))

 

[SimonVOwen]

Hello again,

I have the same dataset that I have used the code for as above to generate the sum value.
However I now have a condition that I need to split out the weekends which are represented in the first column as 1 (weekend) and 0 (day)
I have tried to add a simple IF in front of the sum Index but this would not appear to work as I would like.
What would be the best way to incorporate a condition on a sum index or would I need to incorporate Match?
I have not done a Index/Match before can anyone assist with this?

		A	B	C	D	E	F	G	H	I	J	K	L	M
1	1		1	2	3	4	5	6	7	8	9	10
1	2	1	0.0387	0.0375	0.0342	0.033	0.0317	0.0322	0.0307	0.0303	0.0313	0.0316		0.6636
0	3	2	0.038	0.0366	0.0312	0.0311	0.0348	0.0306	0.0357	0.0326	0.0307	0.0355
0	4	3	0.0403	0.0373	0.0342	0.0337	0.0328	0.0314	0.0313	0.0317	0.0315	0.0321
0	5	4	0.0356	0.032	0.0297	0.0292	0.0285	0.0283	0.0277	0.0279	0.029	0.0295
0	6	5	0.0344	0.0315	0.0294	0.0284	0.028	0.0275	0.0274	0.0273	0.0285	0.0296
0	7	6	0.0348	0.032	0.0296	0.0284	0.0281	0.0279	0.0271	0.027	0.0284	0.0294
1	8	7	0.0349	0.0314	0.0294	0.0289	0.0286	0.0277	0.0272	0.0275	0.0287	0.0292
1	9	8	0.0335	0.034	0.0302	0.0282	0.0298	0.0283	0.0277	0.0296	0.0281	0.0312
0	10	9	0.0355	0.0314	0.03	0.0289	0.0293	0.0283	0.0261	0.0285	0.0275	0.0276
0	11	10	0.0352	0.0313	0.029	0.0284	0.0274	0.027	0.0267	0.0272	0.0279	0.0287
0	12	11	0.035	0.0314	0.029	0.0285	0.0277	0.0276	0.027	0.0274	0.0284	0.0289
0	13	12	0.0342	0.0313	0.029	0.028	0.0274	0.0269	0.0269	0.0269	0.0281	0.0292

<colgroup><col width="64" style="width: 48pt;" span="15">
<tbody>














































































































































































































































</tbody>

 

[barry houdini]

Assuming numbers in C18:L29 (as per your first post but just moved across one column) then you can solve your original problem with this formula

=SUMPRODUCT((C17:L17>=5)*(C17:L17<=7)*(B18:B29>=5),C18:L29)

Then to incorporate a weekend/weekday condition that's just another condition added to that, e.g.

=SUMPRODUCT((C17:L17>=5)*(C17:L17<=7)*(B18:B29>=5)*(A18:A29=1),C18:L29)