Sum between date but date Vertical and Month Name Horizontal

Hello,

Please find attachment

i need sum between two date but my data structure different , please review my attachment and provide me help.

Amit Bhatt Sample File.xlsx

Hi,

Try this formula with your current formatting:

Formula:

Please Login or Register  to view this content.

where the following are named ranges:
- Day: $A$2:$A$32
- MonthYear: $B$1:$Y$1
- StartDate: $AC$3
- EndDate: $AC$4

Hope this helps

Hello,

thanks for your reply but date not fix dear, for actual date you need to check column and row both.

Row has only days not complete date , to make complete date you need to consider Column also .

Row & Column = " 1 " & "Jan -2013" after that complete date come out = 01 - Jan - 2013.

thanks for your solution but its not work for me .

Regard,
Amit Bhatt

Try my solution Amit, this will fix your issue, and checks both row and column for complete date.

Without named ranges, the formula is:

Formula:

Please Login or Register  to view this content.

Hello,

I have already formula for this but i want some short one.

on your both formulas its not working not getting any result value.

please look at below formula its working fine but i want short code for this one.

{=IF((TEXT(AA9,"MMM-YY"))<>(TEXT(AB9,"MMM-YY")),SUM(IF($B$2:$Y$2<=DATE(YEAR(AB9),MONTH(AB9),1)-1-$B$3+1,IF($B$2:$Y$2>AA9-$B$3,IF($B$2:$Y$2>DATE(YEAR(AA9),MONTH(AA9)+1,1)-1-$B$3+1,$B$4:$Y$34))))+SUM(IF($B$2:$Y$2=EOMONTH(AB9,0)-$B$3+1,IF($A$4:$A$34<=DAY(AB9),$B$4:$Y$34)))+SUM(IF($B$2:$Y$2=DATE(YEAR(AA9),MONTH(AA9)+1,1)-1-$B$3+1,IF($A$4:$A$34>=DAY(AA9),$B$4:$Y$34))),SUM(IF($B$2:$Y$2=DATE(YEAR(AA9),MONTH(AA9)+1,1)-1-$B$3+1,IF($A$4:$A$34>=DAY(AA9),$B$4:$Y$34))))}

Thanks, and I get your point Ford

Never mind, it took me 10 minutes of trying to make a formula before I realised the same thing

Works fine for me...

Originally Posted by ajryan88

Works fine for me...

there is no formula in your attachment can you check for me.

Whoops, try this one... Sample File (1).xlsx

Whoops, try this one... Attachment 292469[/QUOTE]

No result ,, check attachment please

Excel.png[QUOTE=ajryan88;3558906]

you can contact me on my skype >> Amit.bti

I don't know what is happening, but this is directly from your attachment: Capture.PNG

EDIT: And no, I won't be contacting you on Skype, they made a forum for helping people with Excel, and I plan on continuing to use it

Originally Posted by ajryan88

I don't know what is happening, but this is directly from your attachment: Attachment 292473

EDIT: And no, I won't be contacting you on Skype, they made a forum for helping people with Excel, and I plan on continuing to use it

Ok, no issue but as per your capture image cursor not on result BOX , and whatever i see on Formula bar that belong to other box.


Capturexx.png

thanks now i find simple solution based on your formula thanks a lot and its work for me perfectly.


so many thanks to you..

No worries. Please don't forget to mark this thread as solved and please click on the * next to my post to say thanks