Jan 11, 2022
Advice on how to do conditional counting but with a status column
Hey folks, I've been working on this issue for a few hours over the last few days and cannot figure out how to solve this database issue elegantly.
Essentially I have a large matrix of orders, which I can't really change the format of for other reasons and I'd like to build a function to count up the different items.
Can anybody help me figure out how I get a total count (formula needed in the yellow cells)?
Details
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Jan 12, 2022
Hi, here's another way using filter and countif
=arrayformula( countif( filter(C2:E5, B2:E5 = "Qeued"), {"Burger"; "Fries"; "Drink"} ) )=arrayformula(countif(filter(C2:E5, B2:E5 = "Qeued"),{"Burger"; "Fries"; "Drink"}))Spacing not important except as visual aide.
Give us a sample doc if you need help implementing.
For me, or other forum contributors to help, I suggest that you share an "editable by anyone with the link" copy of your sheet. Use your sample sheet to demonstrate what you'd like the sheet to do! Here are some tips to help you set up and share a sample doc and get a great answer quickly... Product Experts' Tips for Posting (Have sensitive info in your sheet? Read the tips!)
Ed
Original Poster ZJohns marked this as an answer
All Replies (6)
Jan 11, 2022
Try this out:
=ArrayFormula(Query(split(flatten(B2:B5&"❄️"&C2:E5),"❄️"),"select Col2, count(Col2) where Col2<>'' and Col1='Queued' group by Col2 label count(Col2) '' "))
You may also be interested in this
=ArrayFormula(Query(split(flatten(B2:B5&"❄️"&C2:E5),"❄️"),"select Col2, count(Col2) where Col2<>'' group by Col2 pivot Col1"))
Last edited Jan 11, 2022
Jan 11, 2022
Jan 11, 2022
Hi, here's another way using filter and countif
=arrayformula( countif( filter(C2:E5, B2:E5 = "Qeued"), {"Burger"; "Fries"; "Drink"} ) )=arrayformula(countif(filter(C2:E5, B2:E5 = "Qeued"),{"Burger"; "Fries"; "Drink"}))Spacing not important except as visual aide.
Give us a sample doc if you need help implementing.
For me, or other forum contributors to help, I suggest that you share an "editable by anyone with the link" copy of your sheet. Use your sample sheet to demonstrate what you'd like the sheet to do! Here are some tips to help you set up and share a sample doc and get a great answer quickly... Product Experts' Tips for Posting (Have sensitive info in your sheet? Read the tips!)
Ed
Original Poster ZJohns marked this as an answer
Jan 12, 2022
This works really. Well. I've even been able to add additional filters and create date window forecasts.
Thanks for your help.