Hello everyone,
I'm trying to write a COUNTIF function with the following two criteria:
1) Status is COMPLETE.
2) Date to the left of the status falls within a certain range (i.e. specific quarter).
Please see attached spreadsheet. I'm trying to figure out how to calculate the cells in yellow. I'm sure there must be an elegant solution to this where I can use the whole array as the range...but I'm having difficulty. Appreciate the help.
Chris
Which cells (none in yellow!!)?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
B2-B5. Not sure why they lost their formatting...
Trty this formula in B2 copied down to B5
=COUNTIFS(C$9:I$12,"Complete",B$9:H$12,">="&E2,B$9:H$12,"<="&F2)
Audere est facere
Thanks DLL. That does appear to work. Does it know that it's looking at side-by-side cells because the ranges are offset by 1?
That's right, there's always a "one to one" relationship between the ranges so it's associating C9 with B9 and if all 3 conditions are satisfied for those cells 1 will be added to the total - that means it will also add one to the total if D9 contains "Complete" and C9 contains a valid date.......but presumably that can't be the caseOriginally Posted by costgeek
Yeah, I protected the cells with data validation so that won't happen. You were a big help...I never would have figured that out on my own.
Thanks again,
Chris
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks