COUNTIFS - Resolved

[Abraxus]

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.

[HansV]

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

[Abraxus]

Because there is a column guaranteed to have data, I used your alternate option and it worked like a champ.

Thanks for that suggestion.