I have wrote a index match array formula in columns C & D
the formula in C4 looks at C3 & A4 then the date in D2
what I need Is it to look at a date range so when I change the date it adds up what it has found.
I.E
Ardilla - Cases - between 02/01/14 & 03/01/14 would bring back 19
I have attached the work book for an example.
hi namluke. try this in C4:
=SUMIFS(INDEX(H$4:AY$34,,MATCH($B4,$H$2:$AY$2,0)),$G$4:$G$34,">="&$D$2,$G$4:$G$34,"<="&$E$2)
copy down & across. by the way, it's been a while you've joined & you have probably upgraded your excel version since your file is an xlsx. could you update your profile of your MS Off Ver. it would be clearer & help members give you newer & more efficient formulas
edit: by the way, i dont know where is the date range of 2Jan2014 to 3jan2014. i assume it to be D2 & E2
Last edited by benishiryo; 01-13-2014 at 08:56 PM.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
hi thank you for your reply, it doesnt seem to work, it comes back with 0 - it needs to look up cases as well, so i think it would need to be an array formula.
It need to look up the name (b4) the cases (c4) and the date range (d2 & e2)
Hope this can be done as it would be a great help!
Thank you for your time
So sorry! Yep works perfectly!!
Thanks
Hello,
I am having an issue with the same situation. I am trying to add values on cell C13 between date range based on criteria. But when I am writing the function I got an error.
Could anyone assist me please? I have written this function =SUMIFS(INDEX(D3:G9,,MATCH(B13,B3:B9,0)),D1:G1,">="&B15,D1:G1,"<="&B16)
Main A/c # Sub Account Total Budget May June July August
1111
400001 4000011000 1,900,000 158,316 157,740 160,748 157,224
400001 4000012000 10,057,125 798,818 804,304 959,697 858,529
400001 4000012100 2,819,793 192,303 222,257 275,415 289,158
400001 4000013000 7,650,000 642,183 622,365 730,280 691,559
400001 4000014000 10,645,130 767,988 760,082 1,017,803 954,500
400001 4000015000 9,512,245 647,728 665,092
400001 4000015100 95,520 45,997 49,523
4000011000 #VALUE!
Start month May
End month August
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks