I have a spreadsheet that has data on 1 tab, a summary chart on the other.
1 field in the summary chart has a long convoluted COUNTIFS formula. I was asked to modify it to only show data where 1 of the fields in the data is either A)from this year or B)blank.
I was able to do the year piece, but the blank part eludes me. I was going to do:
=LongCountIfsFormulaIncludingYearPiece + COUNTIFS($MyColumn,"")
but it is counting the blank rows below the data from the data tab so I am getting 1,048,000+ as the result.
Is there a way to count the blanks only within the data when the last row of data is unknown as new data is added each week? I don't want to modify the formulas each week. (There are dozens of them, in actuality)
Thanks!
PS: This is a macro-enabled workbook, if that helps.
COUNTIFS - Resolved
-
- 3StarLounger
- Posts: 254
- Joined: 01 Mar 2010, 17:34
- Location: Blue Springs, MO
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: COUNTIFS
If you can convert the data to a table, you can refer to the table column instead of the sheet column. The table column will grow/shrink dynamically as data are added or deleted.
Alternatively, if there us a column guaranteed to be not blank in the data range, you can use
=...+COUNTIFS($MyColumn, "", $OtherColumn, "<>")
Alternatively, if there us a column guaranteed to be not blank in the data range, you can use
=...+COUNTIFS($MyColumn, "", $OtherColumn, "<>")
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 01 Mar 2010, 17:34
- Location: Blue Springs, MO
Re: COUNTIFS
Because there is a column guaranteed to have data, I used your alternate option and it worked like a champ.
Thanks for that suggestion.
Thanks for that suggestion.
Morgan