How do i fill the table to the right, i want to a count of how many available in january. So the formule had to determine the daterange and only count how many of the available or not available or removed that only correspond with that month or date range?
I know i can use a pivot table but i cant figure out the formula? attached example spreadsheet
In F4 try
=SUMPRODUCT(--(TEXT($A$2:$A$57,"mmmm")=$E4),--($C$2:$C$57=F$3))
Drag down and across
Hi, welcome to the forum
1. If you are using real dates, it is almost always better to stick with real dates everywhere. 1/1/2019 is a real date, but January - to excel - is just a bunch of random numbers. You could enter your months as 1/1/201/, 2/1/2019 if necessary, then just format yo show only months.
2. Check your spelling - you have Feb spelled wrong
3. If you must have just month names (use Pepe's suggestion or)...
F4=COUNTIFS($A:$A,">="&DATEVALUE($E4&" 1, 2019"),$A:$A,"<="&EOMONTH(DATEVALUE($E4&" 1, 2019"),0),$C:$C,F$3)
copied down or across.
OR
4. If you could use real dates...
F4=COUNTIFS($A:$A,">="&$E4,$A:$A,"<="&EOMONTH($E4,0),$C:$C,F$3)
copied down and across
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
Hey Guys, Sorry for the delay in response. this is great stuff, thanks a bunch for this Peppe & FDibbins. I tried all three options, The below is more robust so far, considering i might be using a different year maybe on a different table. I changed to a date and formatted to mmmm. Im going to play around further with this though. Just thinking out loud but maybe there's was a way i could declare the year in a cell as a list dropdown in order to change the year if some my date range was in 2018 for example, is this possible to list a formulae to change the dates in column E date and keep a formatted month display?
F4=COUNTIFS($A:$A,">="&$E4,$A:$A,"<="&EOMONTH($E4,0),$C:$C,F$3)
Yes that is doable. In the attached file cell G1 displays the year based on a selection from a list of years in column M.
Note that if there is a large range of years, say 20 or more, it may be easier to just type in the year than scroll through the drop down.
The formula that populates the cells in column E is: =DATE(G$1,ROW(A1),1)
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks