How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • Go back-and-forth until satisfied
    Rate the answer you receive.
Ask Divyansh Your Own Question
Divyansh
Divyansh, Computer Support Specialist
Category: Programming
Satisfied Customers: 6553
Experience:  Computer Applications Expert
102882208
Type Your Programming Question Here...
Divyansh is online now

I am having issues with a countifs formula in google sheets.

This answer was rated:

I am having issues with a countifs formula in google sheets. I would like get the total orders in a specific time for a specif restaurant ( for 10PM to 4 AM)
I have tried - =COUNTIFS(B:B,"Chema's Chinese - Los Angeles",I:I,">=22:00:00",I:I,"<=04:00:00") and it doesn't work.I have attached the picture of the spreadsheet for more clarity

Hello and welcome to Just Answer Tech Support. I will be assisting you today. Accept my apologies to hear you're experiencing difficulty.

  • Would you please share the link to the Google Doc?
Customer: replied 14 days ago.
https://docs.google.com/spreadsheets/d/1XNj4SRor7MI9izzeDAHfY1WK1sf1cnIsCpq60cj-lM0/edit#gid=0

Thank you. Allow me a moment, please.

Customer: replied 14 days ago.
Ok

Please use the below formula and let me know if it helps.

=ArrayFormula(COUNTIFS(value(replace(I:I,1,11," ")),"<=22:00",value(replace(I:I,1,11," ")),"<04:00"))

Thank you.

Customer: replied 14 days ago.
You just copy and past the formula which was already there!
Customer: replied 14 days ago.
I created that formula, and it didn't include the restaurant filter

I modified your Replace statement in the existing formula. It was missing one parameter.

Can you check if it helps?

To filter the data by the restaurant name, I suggest you create a Pivot table.

Customer: replied 14 days ago.
I tried it but in the pivot table I can't filter by time only as I am provided as in coloum I have time and date

Allow me a moment. I'll share my findings with you.

Customer: replied 14 days ago.
ok
Customer: replied 14 days ago.
Are you still working on it?

Yes. I am working on your solution. I am sorry it's taking a little longer.

I created a copy of the document. Added a new field that contains time. Used the Replace formula to extract time.

Created a pivot table and filtered the data using time. Let me know if it helps.

Click here to see the document.

Divyansh and 4 other Programming Specialists are ready to help you
Customer: replied 14 days ago.
Thank you, ***** ***** just need a working formula

The formula will not be dynamic.

If you make changes to the sheet, the results will not update. You will need to change the range within the formula.