 Count IF

I am trying to create a count if or something along those lines that keeps a running total of assigned jobs. The catch is I can't get the formula to ignore hidden rows, when a job is completed I am hiding the row. And I only want the total to represent current not completed jobs.

Right now my formula is : =COUNTIF(D:D,T1)

Thanks, Curtis

4 Replies

Re: Count IF

@ccombs765 Look into the AGGREGATE function. You can count values or texts in visible cells in an array through =AGGREGATE(2,5,array)

The 2 means it's using COUNT (3 would be COUNTA) and the 5 means it will ignore hidden rows.

Re: Count IF

=SUM(SUBTOTAL(103,OFFSET(A1,ROW(1:50),))*(A1:A50="Nik"))

Aray formula {}, complete with Ctrl + Shift + Enter.

You can find an example in the inserted file.

*It would also work with = SUMPRODUCT  :).

NikolinoDE

I know I don't know anything (Socrates)

Re: Count IF

I transfered the formula over and changed the inputs to suit how I have the sheet set up and it is populating :   #N/A

fomula: =SUM(SUBTOTAL(103,OFFSET(D1,ROW(2:100),))*(D2:D92="Curtis"))

Re: Count IF

It is an array formula, please note when completing.

{=SUM(SUBTOTAL(103,OFFSET(D1,ROW(2:100),))*(D2:D92="Curtis")) }

When you press Ctrl+Shift+Enter, Excel automatically inserts the formula between { } (a pair of opening and closing braces).

Create an array formula

Note: If you have a current version of Microsoft 365, then you can simply enter the formula.

Guidelines and examples of array formulas