Using COUNTIF

Question

Hello!

Using the table below, on a separate worksheet, I am trying to create a formula that would count how many times each error appeared between 8/1-8/4. I have tried using COUNTIFS, but what I am doing keeps returning a VALUE error. Please help!

8/1/2024				Error1	Error2	Error3
8/1/2024				Error1	Error2	Error3	Error4
8/2/2024				Error2	Error1	Error4	Error3	Error5
8/3/2024				Error3	Error2	Error4
8/3/2024				Error5	Error3	Error1
8/3/2024				Error 5	Error4	Error2
8/4/2024				Error2	Error3	Error6	Error7
8/4/2024				Error1	Error2	Error3

Answer 1

Hi,

In cell B14, enter this formula

=SUMPRODUCT(($E$2:$I$9=A14)*($A$2:$A$9>=$A$12)*($A$2:$A$9<=$B$12))

Hope this helps.

Answer 2

I should add that my spreadsheet also has other dates listed on it (unlike my example), and I only want it to return the number of times the error occurred between these dates. How would I tell it to only look for the errors that occurred on those dates?

Also, I have the full list of errors on a separate worksheet and want the count to be entered into a separate cell. This is getting me close to what I need but not exactly!

Answer 3

Hi,

In cell A13, enter this formula

=UNIQUE(TOCOL(E2:I9,3))

In cell B13, enter this formula

=COUNTIF($E$2:$I$9,A13#)

Hope this helps.