Please Correect Formula

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have dates in column I, amount is column J
I am using formula,

'=SUMIFS(J2:J217,I2:I217,">="&DATE(H221,MONTH(I221&" 1"),1),J2:J217,"<="&DATE(H221,MONTH(I221&" 1"),30))

Where H221 has dropdown year and I221 occupies drop down months.
the formula is giving wrong results.

Regards

Sohail
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Try:
=SUMIFS(J2:J217,I2:I217,">="&DATE(H221,MONTH(I221),1),J2:J217,"<="&DATE(H221,MONTH(I221),DAY(EOMONTH(I221,0))))

What does the " 1" do?

BTW .Not every month has 30 days-that;s wahy your formula fail.s
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks Robert,

I am still trying to use your formula, I am getting 0.
the "1" converts, words, January, February etc, to 1, 2, etc.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I have dates in column I, amount is column J
I am using formula,

'=SUMIFS(J2:J217,I2:I217,">="&DATE(H221,MONTH(I221&" 1"),1),J2:J217,"<="&DATE(H221,MONTH(I221&" 1"),30))

Where H221 has dropdown year and I221 occupies drop down months.
the formula is giving wrong results.

Regards

Sohail

What is the value I221 houses?
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks Aladin,

that contains months in words, January, February, March and so on, so the part of formula =month(I221&" "1) converts it to numbers, 1, 2, 3 etc

Thanks
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
X</SPAN></SPAN>
10/22/2012</SPAN></SPAN>
(877.55)</SPAN></SPAN>
X</SPAN></SPAN>
10/22/2012</SPAN></SPAN>
(3,615.24)</SPAN></SPAN>
X</SPAN></SPAN>
10/22/2012</SPAN></SPAN>
(467.35)</SPAN></SPAN>
X</SPAN></SPAN>
10/22/2012</SPAN></SPAN>
(0.05)</SPAN></SPAN>
X</SPAN></SPAN>
11/8/2012</SPAN></SPAN>
129.94 </SPAN></SPAN>
X</SPAN></SPAN>
11/12/2012</SPAN></SPAN>
(4,200.00)</SPAN></SPAN>
X</SPAN></SPAN>
12/7/2012</SPAN></SPAN>
170.14 </SPAN></SPAN>
X</SPAN></SPAN>
12/17/2012</SPAN></SPAN>
171.13 </SPAN></SPAN>
X</SPAN></SPAN>
12/27/2012</SPAN></SPAN>
(1,905.00)</SPAN></SPAN>
X</SPAN></SPAN>
12/27/2012</SPAN></SPAN>
(1,710.65)</SPAN></SPAN>
2012</SPAN></SPAN>
DECEMBER</SPAN></SPAN>
(12,509.20)</SPAN></SPAN>

<TBODY>
</TBODY>
2012 is in H221 and DECEMBER is in I 221.

Thanks
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Thanks Aladin,

that contains months in words, January, February, March and so on, so the part of formula =month(I221&" "1) converts it to numbers, 1, 2, 3 etc

Thanks

X10/22/2012 (877.55)
X10/22/2012 (3,615.24)
X10/22/2012 (467.35)
X10/22/2012 (0.05)
X11/8/2012 129.94
X11/12/2012 (4,200.00)
X12/7/2012 170.14
X12/17/2012 171.13
X12/27/2012 (1,905.00)
X12/27/2012 (1,710.65)
2012DECEMBER (12,509.20)

<tbody>
</tbody>
2012 is in H221 and DECEMBER is in I 221.

Thanks

J221:

=DATE(H221,MONTH(I221&" 1"),1)

Now:

=SUMIFS(J2:J217,I2:I217,">="&J221,I2:I217,"<="&EOMONTH(J221,0))
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
X10/22/2012 (877.55)
X10/22/2012 (3,615.24)
X10/22/2012 (467.35)
X10/22/2012 (0.05)
X11/8/2012 129.94
X11/12/2012 (4,200.00)
X12/7/2012 170.14
X12/17/2012 171.13
X12/27/2012 (1,905.00)
X12/27/2012 (1,710.65)
2012DECEMBER (12,509.20)

<tbody>
</tbody>
2012 is in H221 and DECEMBER is in I 221.

Thanks
Instead of this conversion I would suggest to use Dates with Custom mmmm format- more Excel friendly that the Text equivalent.
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you Aladin,
Thanks you very much.
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you Robert.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top