Count IF

New Contributor

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

@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.

@ccombs765 

=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  :). 

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

@NikolinoDE 

 

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"))

@ccombs765 

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

Download an example workbook with all the array formula examples in this article.

 

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)