Countifs & Dates

[loopey83]

Hello.

I'm struggling to get over what feels like the simplest of issues. I'm trying to get my formula to count how many calls are logged onto a desk by service within a certain time frame.

I was using Countifs and I've tried a combination of putting the date in like "=> 31/05/2021", or using the => =< combo in relation to a cell that has a date reference but no matter what it returns a zero, despite knowing there's info there. I even paired it back to just look for how many things were between a set of dates and again, a return to 0

I've attached my spreadsheet with the last ditch attempt.

Ideal world I'd like to calculate how many tickets are in Status OPEN, between a weekly date, ie 31/may/2021 to 6th June, and belong to the service desk Hubble.

I'm using the insert table option for my data. I don't know if that's what's making it not count stuff.

can you assist?

[JohnTopley]

Try

in B19

=COUNTIFS(Table2[[Date Logged]:[Date Logged]], ">=" & B$18, Table2[[Date Resolved]:[Date Resolved]], "< " &B$18+7,Table2[[SVD Assigned Name]:[SVD Assigned Name]],"=" &$A19)

Drag across and down

[loopey83]

That's picking up some stuff, but not everything

I can't see why it would only pick up 2 of the SVD.

[Slabu]

The Names under weekly totals open needed to match the table exactly for his formula. See attached.

[AliGW]

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

[loopey83]

I managed to get it working using a different formula:

it dawned on me for the table i didn't want to count the number of open per SVD i just wanted to count how many tickets were logged to SVD Assigned (not SVD Assigned Name) for each week.

=(COUNTIFS(Table2[Date Logged], ">=31/05/2021", Table2[Date Logged], "<=06/06/2021", Table2[SVD Assigned], "=HUBBLE BI TEAM"))

However, both his and mine are not picking up everything. for SEAS AP C & VEN - it's not picking up the entry in Row308 it meets the criteria and if i change it to a different week it's picked up in the week i change it to. So i'm not sure what's going on there

[josephteh]

It's not efficient to have names in your Calculation sheet different from the names in your Data.

I have added a column with the correct names in column B, you may hide the column for aesthetic reason.

Formula for cell C7:

Please Login or Register  to view this content.

Formula for cell C19:

Please Login or Register  to view this content.

Formula for cell C30:

Please Login or Register  to view this content.

[loopey83]

Thank you everyone. From tiding up formatting and looking at your formula i've finally managed to get a working version, all the random gremlins are gone too.

[JohnTopley]

As pointed out previously, the names in column A need to match those in your table.

Correct names in column A i.e replace with those in B (post #7)

[josephteh]

loopey83, it's not efficient to hardcode the names and dates in your formulas. Your formulas cannot auto-adjust when you copy to new weeks across or add new names below.

[loopey83]

I can't get the formula to work properly when it's not hardcoded that's the issue.

So for the table Weekly calls open - I started looking to create the wrong information. I had planned to use this table to see how many open calls per week - but given most of these were closed quickly after opening it wasn't showing me what I wanted.

So i changed that table to look for only the number of calls logged per SVD assigned (not SVD name)

For table 2 - I'm wanting to see how many calls were closed that week. the calculation without the dates hard coded worked for the most part but they don't tally up and I can't work out why.

In total for June (including 31 May as the week begins on that date) there were 531 closed calls. In the spreadsheet you provided it shows 468 closed, so it's not picking up everything.

[josephteh]

I will look at my formulas again if attached amended file is still not what you want.

First, some clarifications:

Table from row 18
"Weekly totals Open" for week commencing 31-May
- what is the figures you want to show?
- [Date Logged] between 31-May and 06-Jun?
- And, with Status "OPEN"?

Table from row 29
Weekly totals closed for week commencing 31-May
- [Date Logged] between 31-May and 06-Jun?
- And, with Status "CLOSED"?

If above are correct, then [Date Resolved] has no place in the formulas.

Please see amended file attached. I have change from COUNTIFS formulas to SUMPRODUCT formulas.

[josephteh]

New formulas used:

Cell B19:

Please Login or Register  to view this content.

Cell B30:

Please Login or Register  to view this content.

[loopey83]

That is exactly what I wanted. thank you so much.

It was starting to drive me a wee bit insane with little bits not working.