Sumif formula help

[ganeshpoojary05]

Hi all,

I need a help in sumif formula. I have a data as below. i need a sumif formula help:

For example:

i need a formula where i can get a sumif for 5031 to 5034 for the below data, the result should be 2022


<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=448 border=0><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>5021</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64>5031</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64>5032</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64>5033</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64>5034</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64>5039</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64>5041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>256</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>525</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>555</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>456</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>486</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>526</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>4545</TD></TR></TBODY></TABLE>

 

[Matty]

Assuming your data resides in A1:G2, try:

=SUMPRODUCT(--(A1:G1>=5031),--(A1:G1<=5034),A2:G2)

Matty

 

[texasalynn]

try this

Excel Workbook
	I	J	K	L	M	N	O
1	5021	5031	5032	5033	5034	5039	5041
2	256	525	555	456	486	526	4545
3
4	2022
Sheet2

Excel 2003

Cell Formulas
Range	Formula
I4	=SUMPRODUCT(--(I1:O1>=5031),--(I1:O1<=5034),(I2:O2))

 

[Colin Legg]

In Excel 2003 and earlier:

=SUMIF(A1:G1, ">=5031", A2:G2) - SUMIF(A1:G1, ">5034", A2:G2)

In Excel 2007 and later:

=SUMIFS(A2:G2, A1:G1, ">=5031", A1:G1, "<=5034")

 

[snoopyhr]

here is something dynamic

Excel Workbook
	A	B	C	D	E	F	G
1	5021	5031	5032	5033	5034	5039	5041
2	256	525	555	456	486	526	4545
3
4
5	Range
6	Start	End	Sum
7	5021	5034	2278
Sheet1

#VALUE!

 

[ganeshpoojary05]

Thanks all for your wonderful support.