COUNTIF and HAS

Cheryl Collins

Hi

I'm trying to find the best way of counting particular options from a drop down list on another sheet by a certain date. The formula I have so far is:

=COUNTIFS(HAS({Job Log l Job Type}, "CD Creation"), HAS({Job Log l Job Type}, "CD Extraction", HAS({Job Log l Job Type}, "Scan & post", HAS({Job Log l Job Type}, "USB Creation", HAS({Job Log l Job Type}, "USB extraction")))), {Job Log l Date} = Date@row)

My reference sheet is called Job Log.

I want to be able to reference this sheet to count the number of; CD Creation, Extraction, Scan & Post, USB Creation / extraction jobs for a particular date:

Job Log Sheet screen shot

SLA Tracker Sheet screen shot

When I get a formula that works I would expect to see the figure 2 against 07/11/22 and 3 against 08/11/22 in my SLA tracker above.

Are formulas the best thing for this type of thing or is there a better way of doing this?

TIA

Cheryl

Cheryl Collins

Hi @Nick Korna

Thanks so much for your response.

I have added your formula into my SLA sheet and updated the sheet references but, I'm still getting a #UNPARESABLE error. Could this be because the Date Received column in my Job Log sheet is a Created Date column type so, it includes the time as well as the date?

TIA

Cheryl

Nick Korna

Hi @Cheryl Collins,

You can certainly do this with formulas.

In your number of items column on the SLA tracker the formula would be:

=COUNTIFS({Job Log Range Date}, Date@row, {Job Log Type}, ("CD Creation")) + COUNTIFS({Job Log Range Date}, Date@row, {Job Log Type}, ("CD extraction")) + COUNTIFS({Job Log Range Date}, Date@row, {Job Log Type}, ("Print & Post")) + COUNTIFS({Job Log Range Date}, Date@row, {Job Log Type}, ("USB Creation")) + COUNTIFS({Job Log Range Date}, Date@row, {Job Log Type}, ("USB extraction"))

With the cross sheet references looking like this:

I've left out the majority of the empty columns from the Job Log, but as the ranges are a single column each these won't affect anything.

Using your sample data:

Job log:

SLA tracker:

The only assumption I've made here is that the Scan & post mentioned should be print & post as shown in your screenshot - otherwise your numbers would be 2 for both dates.

Hope this helps, but if I've misunderstood anything or you've any further questions just post! :)

Cheryl Collins

Hi @Nick Korna

Thanks so much for your response.

I have added your formula into my SLA sheet and updated the sheet references but, I'm still getting a #UNPARESABLE error. Could this be because the Date Received column in my Job Log sheet is a Created Date column type so, it includes the time as well as the date?

TIA

Cheryl

Nick Korna

@Cheryl Collins

The Date Received column being a created date instead of a date type shouldn't make any difference to the formula - it functions fine in the test sheet I made if I switch it from one to the other.

I think the error is possibly caused by a missing comma in the last part of the formula:

Can you try popping one in and see if that resolves the issue?

Cheryl Collins

@Nick Korna

Nailed it! 😊 That's worked. Thank you so much. I really appreciated it.

Cheryl

Nick Korna

No problem, happy to help! ☺️