Forum Discussion
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
=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.
I know I don't know anything (Socrates)
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"))
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).
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.
I know I don't know anything (Socrates)
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.
