Google sheet CountIF formula not working, and how to create changing charts

[loopey83]

Morning

I'm having difficulty with google sheets and using the find and replace/count if with dates.

I'm using the formula along the lines of =countifs('Helpdesk Raw Data'!$B:$B, "=11/01/2021",'Helpdesk Raw Data'!$J:$J,"=SEAS SYS ADMIN") to find stuff in a specific date.

I've tried to use the find using a specific cell so I can copy it along and it the cell i'm looking to match the date with changes as you go along the row (so, B4 looks for date in B1, C4 looks for the date in C1 etc) with the formula but it's not working. So instead of the "=17/01/2021" I was hoping it would pick up the from the cell Ax where x is the date in that row, and instead of "=HUBBLE BI TEAM" it searches for cell "=$A$9"

I've included a link to a short snippet of the data where all personal info is stripped out, which doesn't impact what I'm looking for and you should be able to edit, if i've set the permissions correctly.

https://docs.google.com/spreadsheets...it?usp=sharing

My Second question is:
Ultimately the data we are tracking is going to have daily, monthly and yearly data. Is it possible in google sheets to create a chart that will automatically circulate between the three sets of data or have a drop down, so the user looking could pick the dropdown daily, and it shows the daily data, then clicks the yearly button to see the yearly data?

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[loopey83]

apologies.

Changed it now

[AliGW]

Much better - thanks.

[MrShorty]

I'm not sure I understand what you want with the countifs() function. Once I made sure that the date in b1 matched a date in column b of help desk raw data, it seemed to work just fine. (I'm not as familiar with sheets, but my edits may have stuck, so others will not see the original file). From what I saw in your sample file, the main key to the countifs() function is to make sure you have matching date values, and maybe remembering that spreadsheets store dates and times as numbers so that your are making sure the numbers match.

Ad for the chart thing, I'm not sure what sheets pivot tools offer. On Excel, I would recommend a pivot table where you can select how the date field is grouped -- by day, or month.

[loopey83]

Apologies for the delay in responding. The notification got caught in my spam filter.

for my countifs - I'm counting how many tickets are logged on a specific date for a specific team.

So for the example data. I'd be looking for it to look at SVD assigned, find SEAS SYS ADMIN, then check the date - which I have got working by using the formula

=countifs('Helpdesk Raw Data'!B:B,"=07/12/2018",'Helpdesk Raw Data'!J:J, "=SEAS SYS ADMIN")

However, for the two underlined part of the formula, i want it to look at the specific cell that info is contained. So instead of "=07/12/2018" I want it to check the date in Row 1 and see if it matches as that row already contains the dates - and in google sheets if i copy and paste the formula over the rest of the cells, it retains the 07/12/2018 date in every cell where i'd want it to increase as it went along.

So for the data on link. I'd want it to look at sheet2 cell B1 for the date instead of me typing in 01/01/2021 and instead of me writing SEAS SYS ADMIN in every cell, the test data would look at Cell A9.

You can see my attempt in Cells B & C 9. When i hardcode the date. it picks it up, when i try to point it to specific cells it counts as 0

[MrShorty]

I'm not sure what difficulty you are having using references for the criteria. I can't remember if it was my edits or your original, but the current "="&B1 should work. If you were using "=B1" before my edits, that searches for the text string B1 not the cell reference. Is that the heart of this part of the question.

[loopey83]

That is exactly what i was looking for! i knew it might be something incredibly silly that i missed