CODEWITHSUNDEEP
excelexcel-formula
Grosi
Grosi

Reputation: 152

Count the number of rows in an array which met a criteria

I have a training records spreadsheet where i have the training modules in columns and the colleagues in rows. There is a complex department with many tasks where I want to count how many people have been trained on a specific day.

In other words, i would like to know within the array how many rows have the same value. I've tried the below code but it gave me back the number of tasks which have been trained on that day:

=SUMPRODUCT(--($FZ$9:$GY$1915=DATEVALUE("01/02/2018")))

This is a more simple example of what I need:

    Task1   Task2
-------------------
    Date1   Date2
    Date1   Date1
    Date2   Date1

If I want to look for Date1, the result should be 3, because it appears in 3 rows. When I put the below mentioned countif/sumif CSE formula, it counts how many times Date1 appears, which is 4.

Upvotes: 0

Views: 250

Answers (2)

QHarr
QHarr

Reputation: 84465

I think this is one of the cases where you could benefit from a helper column. And example formula would be, for given example:

Cell C2:

=--OR(A2="Date1",B2="Date1")

Drag down (autofill if table)

Formula in D2:

=COUNTIF(C2:C4,1)

Example in sheet:

Example data

Upvotes: 2

Aritesh
Aritesh

Reputation: 2103

You can use array Formulas. To do that, once you type the formula, press Ctrl + Shift + Enter togther. The formula you will use is - 

=sum(if($FZ$9:$GY$1915=DATEVALUE("01/02/2018"),1,0))

See the example below -enter image description here

Upvotes: 0

Related Questions