question

Walker-1054 avatar image
0 Votes"
Walker-1054 asked emilyhua-msft commented

Correct formual to add number of times a date appears?

Hello

I am trying to figure out how to calculate the number of time a date appears.

120336-capture.png
120401-capture-2.png

The first image shows the formula I am using which bring a value of 0, and the second image shows the referencing tab.

What am I doing wrong?

Thanks :)



office-excel-itpro
capture.png (15.3 KiB)
capture-2.png (26.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft commented

@Walker-1054

Please select the cell with "2/08/2021" in spreadsheet Y7Q4 - October 21, then check the content shown on Formula Bar.
And then right-click the cell, go to Format cell, check the format.
For better analysis, you may take a screenshot.

Besides, I would suggest you try the formula =COUNTIF('Y7Q4 - October 21'!D:D,'Y7Q4 - October 21'!D154) for a test, as I see the data in cell D154 is "2/08/2021".


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you - when I selected the date cell in the other workbook, it also had a date included. Once removed, the formula worked.

Follow up question though, what would be the correct formula to use if using 2 different criteria's? I thought it would be this:

=COUNTIFS('Y7Q4 August - October 21'!D:D,"2/08/2021",['Y7Q4 August - October 21'!AK:AK,"Complaints"])

The second criteria is to read from the same spreadsheet as the date and use that information alongside the second criteria of complaints.

As in if on "2/08/2021" there were "32 Complaints"

Thanks :)

0 Votes 0 ·

@Walker-1054

Yes, Countifis function could be helpful on multi-condition count.
Please try the following formula.
=COUNTIFS('Y7Q4 August - October 21'!D:D,"2/08/2021",'Y7Q4 August - October 21'!AK:AK,"Complaints")

0 Votes 0 ·