Hello!
Here is the formula I'm playing around with: =SUMPRODUCT((MONTH($A$22:$A$28)=10)*($B$22:$B$28)).
This works fine until any textual data or blanks show up in Date Column A. What can I do to get the target cell to calculate all data for the month of October while omitting anything else?
I've tried many things over the last few hours and I just can't get it to work. Any help would be greatly appreciated. Let me know if you need more info.
Thank you!
Excel Image.jpg
It would help the people who might be working on this if you could "Go Advanced" and upload the file from which your screen shots are taken.
Attempting to upload the Excel file...
I put this array formula (Ctrl + Shift + Enter) in B13 and it yielded the answer that I expected.
Take a look at the modified file and see if it does what you want:
Please Login or Register to view this content.
Copy of Metrics Test.xlsx
Let me know if you have any questions.
Last edited by JeteMc; 10-12-2015 at 08:44 PM. Reason: explanation of formula
I would take a different approach.
1. Use real dates in your summary table (A4:A15)....1/1/15, 2/1/15, etc), you can format Custom "mmmm" to show the month names like you have.
2. Use this, copied down...
=SUMIFS($B$22:$B$28,$A$22:$A$28,">="&$H4,$A$22:$A$28,"<"&EDATE($H4,1))
Note, I put my dates in H4:H15 to compare with your answers - you will need to adjust my formula to look at A4:A15 after you use real dates there
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Perfect! This is exactly what I was looking for. I didn't know if the "IFERROR" would catch blanks, text, etc. Thank you SO, SO much! I've already tested it across different months...works great! Cheers, good human!
I will try this method as well...always good to learn more! Thank you very much!
You're welcome, and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools above your first post. I am glad to see that you are investigating both methods -- Hope that you have a nice day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks