0

enter image description hereExample scenario: (picture added)

I want to find out how many people had reason "No access to accommodate patient" at location A during February 2022. (more like filling up the table)

How will I find this is google sheets? Using countif or some other formula?

Can someone please help me with a formula?

4
  • COUNTIFS() is the right approach. What are you having trouble with exactly? You need 4 conditions: 1st is location A, 2nd is reason xyz, 3rd is date > start date, 4th is date < end date. If you still can't figure it out please share a sample sheet so the volunteer contributors here can demonstrate a solution.
    – ztiaa
    Feb 24 at 0:32
  • Hi and welcome to WebApps. I have a data sheet of 100+ reasons and dates for 3 locations This is a lot of data and many permutations, so it could be a complex series of reports. There are many ways to approach your question (@ztiaa has outlined one perfectly reasonable method), but the most appropriate solution will depend on how you want to display the results. Would you please edit your question to describe the number of rows of raw data, and also please provide an example of how the results of a successful answer would be displayed.
    – Tedinoz
    Feb 24 at 1:45
  • I agree with ztiaa and Tedinoz. We can't see your spreadsheet, layout, ranges, data, data types, options, etc.; and we don't know your end goal as far as how you will set options/choices, where the output will go, what that output should look like. An image with two lines of limited data is not enough to convey all of the details. Do share a link to your spreadsheet (or to a copy of it), so that those here can see all of the factors involved.
    – Erik Tyler
    Feb 24 at 3:26
  • Sorry about that - just added a picture for better understanding!
    – user291835
    Feb 24 at 6:18

1 Answer 1

0

There are a number of ways to resolve your problem, and several precedents included in WebApps and StackOverflow. But, as is common, your data doesn't directly align with any of the examples, and this answer is offered to assist you.

The easiest approach is to generate a pivot table. This can be done from the main menu (Insert, Pivot table) or from a QUERY. Since the pivot is date sensitive, I chosen to display the QUERY approach.


Set the data range:

  • Cell F1: enter the start date of the relevant month
  • Cell F2 is a formula: =eomonth(F1,0)

You can use formatting to display the the Month and Year in cell F1 and "hide" the contents of Cell F2.

The query is in cell F2:

{{query(A1:D,"select C, count(D) where A is not null and B>=date'"&text($F$1,"YYYY-MM-DD")&"' and B<=date'"&text($G$1,"YYYY-MM-DD")&"' group by C pivot A",1), query(A1:D,"select count(D) where A is not null and B>=date'"&text($F$1,"YYYY-MM-DD")&"' and B<=date'"&text($G$1,"YYYY-MM-DD")&"' group by C label count(D) 'TOTAL'",1) };{ "Monthly totals ",transpose(query(A1:D23,"select count(D) where A is not Null and B>=date'"&text($F$1,"YYYY-MM-DD")&"' and B<=date'"&text($G$1,"YYYY-MM-DD")&"' group by A label count(D) '' ",0)), sum(query(A1:D,"select count(D) where A is not null and B>=date'"&text($F$1,"YYYY-MM-DD")&"' and B<=date'"&text($G$1,"YYYY-MM-DD")&"' group by C label count(D) ''",1))}}


Logic and Structure

Date Range The query filters on the date that was entered in cell F1, and the calculated "end-of-month" date in cell G1

  • B>=date'"&text($F$1,"YYYY-MM-DD")&"' and B<=date'"&text($G$1,"YYYY-MM-DD")&"'

Nested queries

  • The PIVOT: query(A1:D,"select C, count(D) ..... group by C pivot A",1)
    • this counts the "Patients", groups them by "Reason" and then created the values per "Location"
  • other queries calculate the sum of the rows and sum of the columns.

EXAMPLE

Pivot


Data

data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.