probs w/ formulas containing "blank"

[Julz]

Good day!

Need to replace instances of "1" with "blank", but am unsure how to
change the formula.

=SUMIF(monthopen!$U$4:$U$20014,1,monthopen!$G$4:$G$20014)/COUNTIF(monthopen!$U$4:$U$20014,1)

Thanx in advance,
~Julz

 

[JulieD]

Hi

if you want to use the word "blank" do this:

=SUMIF(monthopen!$U$4:$U$20014,"blank",monthopen!$G$4:$G$20014)/COUNTIF(monthopen!$U$4:$U$20014,"blank")

if you want to use nothing (ie when the cell is empty) use:

=SUMIF(monthopen!$U$4:$U$20014,"",monthopen!$G$4:$G$20014)/COUNTIF(monthopen!$U$4:$U$20014,"")

Cheers
JulieD

Good day!
Need to replace instances of "1" with "blank", but am unsure how to change the formula.

=SUMIF(monthopen!$U$4:$U$20014,1,monthopen!$G$4:$G$20014)/COUNTIF(monthopen!$U$4:$U$20014,1)

Thanx in advance,
~Julz

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(monthopen!$U$4:$U$20014=""),monthopen!$G$4:$G$20014)/SUM
PRODUCT(--(monthopen!$U$4:$U$20014=""))

or the array formula (entered with CTRL+SHIFT+ENTER)
=AVERAGE(IF(monthopen!$U$4:$U$20014="",monthopen!$G$4:$G$20014))

 

[Julz]

Thanx Frank!! Works like a charm.

How about this one. Same thing. Replace the 1's with blank.
=SUMPRODUCT((monthopen!$U$3:$U$20001=1)*(monthopen!$G$3:$G$20001<11))/COUNTIF(monthopen!$U$3:$U$20001,1)

 

[Julz]

I actually need the same for this one.
=SUMPRODUCT((monthopen!$U$3:$U$20014=1)*(monthopen!$N$3:$N$20014<16)*(monthopen!$T$3:$T$20014="N"))/COUNTIF(monthopen!$U$3:$U$20014,1)

I tried to apply the same logic, but that didn't work.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((monthopen!$U$3:$U$20014="")*(monthopen!$N$3:$N$20014<16)*(
monthopen!$T$3:$T$20014="N"))/SUMPRODUCT(--(monthopen!$U$3:$U$20014="")

if i understood you correctly

 

[Julz]

I'm not sure on this one. It should be returning a value in the 90+%.

Here's what I'm really trying to do.
If the corresponding cell in row U is blank and row T =N, then I need to
sum anything in column N that's <16 and then divide that sum by the count
of cells in U that are blank.

Thanx in advance,
~Julz

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((U1:U1000="")*(T1:T1000="N")*(N1:N1000<16),N1:N1000)/SUMPRO
DUCT(--(U1:U1000=""))

 

[Julz]

Perfecto!! As always!!

Many thanx!
~Julz

Hi
try
=SUMPRODUCT((U1:U1000="")*(T1:T1000="N")*(N1:N1000<16),N1:N1000)/SUMPRO
DUCT(--(U1:U1000=""))