The Detail tab has the data that I'm trying to pull the information from and placing the information in separate tabs based on the month (ie. Jan, Feb, Mar ...). I've figured out the formula on how to pull the data for the matching criteria, but I need to sperate by month.
Thanks in advance!!
For B2, how aboutFormula:
Please Login or Register to view this content.
Shifty58 would you be OK with some added helper columns in the Detail sheet?
Dave
Rather than helper columns in the data source try as in the attached. With sheets Jan and Feb grouped insert a row in row 1.
Then with those sheets still grouped this formula in cell A1 of Jan sheet will copy down through all the grouped sheets a giving a 1st of the month reference relative to each sheet in question.Format as desired.Formula:Please Login or Register to view this content.
Then with sheets still grouped this in B3 copied down and across to D8 should also get you started.Formula:Please Login or Register to view this content.
The SUMIFS works great in columns B, C and D! Can you look at columns E and F?
Also column G. Sorry
The syntax for AVERAGEIFS is pretty much the same as for SUMIFS. Ditto COUNTIFS. I thought that would give you a good start figuring out the rest.
This isn't a homework assignment is it?
So in E3 and filled down.in F3Formula:Please Login or Register to view this content.G3Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 12-06-2022 at 05:45 PM. Reason: Afterthoughts
All 3 formulas returned the value of 0.
How aboutFormula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
G3 is returning a value of 0 (zero).
E3 and F3 are working properly.
What formula do you have in G3?
=COUNTIFS(Detail!$M$2:$M$506,TEXTAFTER($A3,"-"),Detail!$A$2:$A$506,">"&EOMONTH($A$1,-1),Detail!$A$2:$A$506,"<="&EOMONTH($A$1,0))
What is in A1?
My apologies, that formula was suggested by FlameRetired. A1 is the word "Date". Maybe you might want to look at column G in the original spreadsheet.
For that formula A1 needs to be a date, not the word Date.
They all work at my end. Please see the attached.Originally Posted by Shifty58
Row A contains my column headings, which I need to keep. What do I need to change in the formula for it to work?
Also I don't believe TEXTAFTER($A3,"-") is correct since my dates are formatted in the following way. 1/25/2022 not 1-25-2022.
That part of the formula is not referencing a date. It contains Total Knee - 1 which appears to refer to procedure "1" which is a knee procedure. The rest in column A (sheets Jan:Feb) are:
Total Hip - 2
Total Shoulder - 3
Spine - 4
Bilat. Total Knee - 5
Bilat. Total Hip - 6
In my original formula I identified the numbers 1-6 (see below). All I need is for the formula to pull the data by number (1-6) and the month.
G2 =COUNTIF(Detail!$M$2:$M$506,1)
G3 =COUNTIF(Detail!$M$2:$M$506,2)
G4 =COUNTIF(Detail!$M$2:$M$506,3)
G5 =COUNTIF(Detail!$M$2:$M$506,4)
G6 =COUNTIF(Detail!$M$2:$M$506,5)
G7 =COUNTIF(Detail!$M$2:$M$506,6)
Looking at the file attached to post #1, try the following in G2 on the Jan sheet:Formula:Please Login or Register to view this content.
For the Feb sheet:Formula:Please Login or Register to view this content.
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