Jan 10 2022 11:59 AM
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
Jan 10 2022 12:07 PM
@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.
Jan 10 2022 12:28 PM
=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)
Jan 10 2022 01:18 PM
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"))
Jan 11 2022 12:28 AM
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)