LinkBack URL
About LinkBacks
i am using a formula to count of value match item and date. kindly help me where i am missing this b/C formula is not giving result proper. kindly mention both Criteria Feb and Feb-15.
Thanks.
L2:
=SUMIFS(B3:G7,A3:A7,I2,B1:G1,"="&EOMONTH(K1,0))
i am using a formula to count of value match item and date. kindly help me where i am missing this b/C formula is not giving result proper. kindly mention both Criteria Feb and Feb-15.
Thanks.
L2:
=SUMIFS(B3:G7,A3:A7,I2,B1:G1,"="&EOMONTH(K1,0))
Try this:
=SUMPRODUCT(B3:G7,(A3:A7=I2)*(B1:G1=EOMONTH(K1,0)))
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi AliGW,
Thanks for your reply, its working perfect.
But whereabouts only Feb.
What does that mean?But whereabouts only Feb.
And what on Earth does this mean?
No clue what you are trying to achieve. Sorry!kindly mention both Criteria Feb and Feb-15.
your formula working on Feb-15, if i use only month Feb then how this formula work?
Why do you need to do that? All data is February 2015 anyway. You need to be clear about your reasoning.
i have so many others year data in this file. i share just sample so that i need only Month.
Hi AliGW,
i am using your formula but i increased column to find value, formula is not working. can you tell me where is missing?
see the attached file.
Paste the following in J4:Formula:
Please Login or Register to view this content.
Drag the fill handle over to cell L4,
While J4:L4 are still selected drag the fill handle down to L8.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi JeteMc,
perfect working, but tell me on thing i use only Month like Jan, Feb, Mar then this formula is not working, can you tell me how to handle this?
If I understand the question correctly, typing in the month name, or abbreviation, doesn't work because EOMONTH needs a numerical value in the first argument.
Perhaps this article will help: https://support.office.com/en-us/art...rs=en-US&ad=US
If I have misunderstood the question, please update and re-upload the file to demonstrate where the formula is not working.
Let us know if you have any questions.
please see the new file where i enter Jan, Feb and Mar then result are #value.
"Jan", "Feb" and "Mar" are text strings not numeric values so the EOMONTH function returns the #VALUE error.
Is there some reason that the formula from post #9 applied to the file attached to post #8 is problematic?
Let us know if you have any questions.
It occurs to me that you may be looking to sum sales from Jan, Feb or March of multiple years.
To do that you could use the following (as modeled in the attached file):Formula:Please Login or Register to view this content.
Note that the dates are still in J2:L2, however the custom formatting is changed to mmm.
Let us know if you have any questions.
Hi JeteMc,
thanks for guidance, really fantastic,
thanks a lot,
can you help me more in it, selected date data can be find out by this formula?
please see the file start date and end date. if change end date formula return on end data
Try the following:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Hi JeteMC,
its super really, kindly tell me i am comparing Jan 2015 and Jan 2016 both up to June-15 and June-2016, i am using this to find Jan 2016
figure but failed. can you tell me where i am missing?
=IF(AND(O$11>=$R$10,O$11<=EOMONTH($U$10,0)),SUMPRODUCT($B$4:$M$8,($A$4:$A$8=$N12)*($B$2:$M$2=EOMONTH(O$11,0))*(year(b2:m2=year(p11)),"")
currently formula is working perfect, i want to choose as start date and end date Jan 15 to Apr - 2016.
see the file.
thanks
Change the date in U10 from 01/04/2015 to 01/04/2016:
Excel 2016 (Windows) 32 bit
Start Date End Date supriment powder 18000 2000 5500 0 8900 9000 6000 7500 900 9000 integrity shower 5000 7500 7500 0 5500 6000 7000 6500 6000 8000 manpolat plate 7000 6500 2000 0 7500 8000 5000 4000 700 7000 single chamber 1000 2500 1500 0 8000 9000 11000 15000 750 5000 dual chamber 9000 11000 2000 0 9000 10000 12400 20000 1100 7000
Sheet: Sheet1
If you want to hide columns after April 2016, change the formula in O12 to this:
=IF(P$11>$U$10,"",IF(AND(O$11>=$R$10,O$11<=EOMONTH($U$10,0)),SUMPRODUCT($B$4:$M$8,($A$4:$A$8=$N12)*($B$2:$M$2=EOMONTH(O$11,0))),""))
Excel 2016 (Windows) 32 bit
Start Date End Date supriment powder 18000 2000 5500 0 8900 9000 6000 7500 integrity shower 5000 7500 7500 0 5500 6000 7000 6500 manpolat plate 7000 6500 2000 0 7500 8000 5000 4000 single chamber 1000 2500 1500 0 8000 9000 11000 15000 dual chamber 9000 11000 2000 0 9000 10000 12400 20000
Sheet: Sheet1
Hi Ali GW,
see the snap Feb-2016 fugue are ZERO and show June-2015 data.it will show data upto Jan-15 to Apr-2016
Look at post #19.
In future, instead of drip-feeding us the real data, present us with it all at the outset. If we had had the file in post #17 right at the start, this would have been solved by now.
The value in E2 is incorrect. It says this: 28/02/2016
2016 was a leap year, so change it to this: 29/02/2016
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
Start Date End Date supriment powder 18000 2000 5500 6000 8900 9000 6000 7500 integrity shower 5000 7500 7500 7000 5500 6000 7000 6500 manpolat plate 7000 6500 2000 3100 7500 8000 5000 4000 single chamber 1000 2500 1500 2000 8000 9000 11000 15000 dual chamber 9000 11000 2000 1800 9000 10000 12400 20000
Sheet: Sheet1
Last edited by AliGW; 03-09-2019 at 02:35 AM. Reason: Typo corrected.
its also near to solve this, but your formula is working perfect but i dont know why Feb-16 data is still ZERO showing? can you tell me?
Thanks
Look at post #21.
Thank for the guidance,
Special thanks to JeteMC, really fantastic working Boss,
AliGW you also help me to solve this. thanks a lot both of you.
thanks again.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks