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.
By continuing to use this site you consent to the use of cookies on your device as described in our cookie policy unless you have disabled them.
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
Divyansh

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?
Ask Your Own Programming Question
Customer: replied 14 days ago.
https://docs.google.com/spreadsheets/d/1XNj4SRor7MI9izzeDAHfY1WK1sf1cnIsCpq60cj-lM0/edit#gid=0

Thank you. Allow me a moment, please.

Ask Your Own Programming Question
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.

Ask Your Own Programming Question
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?

Ask Your Own Programming Question

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

Ask Your Own Programming Question
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.

Ask Your Own Programming Question
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.

Ask Your Own Programming Question

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
Ask your own question now
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.

Ask Your Own Programming Question