Exclude empty cells from a calculation

[Andrew87..]

Hi,

would you be so kind and assist me with updating my formula to not count empty cells?

=SUMPRODUCT(1/COUNTIF(G2:G51;G2:G51)*(J2:J51=N2))

I would like to have amount of unique values based on another criteria. Row G would be G2:G2000, however cells below G51 are currently empty and formula would show an error.

Thank you very much.

[zbor]

Does this works:

=SUMPRODUCT((J2:J2000=N2)*(G2:G2000<>"")/COUNTIF(G2:G2000,G2:G2000&"")) 

[sktneer]

Or you may use IFERROR to exclude errors like this.....

=SUMPRODUCT(IFERROR(1/COUNTIF(G2:G2000,G2:G2000)*(J2:J2000=N2),0))

Don't forget to confirm it with Ctrl+Shift+Enter instead of just Enter as this is an Array Formula.

Does this help?

[Andrew87..]

Both solutions work. Thank you very much to both of you!

[sktneer]

You're welcome. Glad we could help.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

[Andrew87..]

It created an issue though, if a number in G row is present in multiple dates (J row) , results are slightly inaccurate, such as 59.833 instead of what should be 62. Is there a way for the unique values would be counted every time for specific date (J row) ? So let's say number 1000 is present under 20th and 21st december and I would this number to be counted as unique under both. I hope I am explaining it well enough.

Thanks in advance.

[sktneer]

Maybe this Array Formula (requires CSE)

=SUMPRODUCT(IFERROR(1/COUNTIFS(G2:G2000,G2:G2000,J2:J2000,N2)*(J2:J2000=N2),0))

[Andrew87..]

You are a star. Thanks again. Thread solved.

[martindwilson]

am i missing something i get non integers as answers how can unique count be 2.5?

[Andrew87..]

Hi,

in J I have this formula: =IF(G52>1;J51;"") it adds date from the above cell. The formulas above count correctly in my case as when cell G is filled in, J gets filled in automatically.