SUMIF to average on multiple criteria

[Julz]

Good day everyone!
Yes, I'm back!!

I could use some help with this averaging formula. I need it to now
include only rows where closed!$A$S:$A$S=1

=SUMIF(closed!$G$2:$G$19999,"<3",closed!$P$2:$P$19999)/$P$4

thanx in advance!
~Julz

 

[Bob Phillips]

=SUMPRODUCT((closed!$G$2:$G$19999<3)*( closed!$A$S:$A$S=1 ),(closed!$P$2:$P$19999))/$P$4

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Good day everyone!
Yes, I'm back!!
I could use some help with this averaging formula. I need it to now include only rows where closed!$A$S:$A$S=1

=SUMIF(closed!$G$2:$G$19999,"<3",closed!$P$2:$P$19999)/$P$4

thanx in advance!
~Julz

 

[Julz]

Thanx Bob, I unfortunately asked for the wrong thing. Let's try this
again.

when closed!$A$S:$A$S=1 sum closed!$P$2:$P$19999 and divide by $P$4

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(closed!$A$2:$A$19999=1 ),(closed!$P$2:$P$19999))/$P$4

not quite sure what you mean with a reference like $A$S

 

[Jason Morin]

How about just using SUMIF:

=SUMIF(closed!$AS$2:$AS$19999,1,closed!$P$2:$P$19999)/$P$4

HTH
Jason
Atlanta, GA

 

[Julz]

PERFECTO.
I meant $as... just a little fat fingering of the keys this morning.

Thanx Frank!

 

[Julz]

i love it when there's more than 1 way to solve a problem.

many thanx!!
~j

 

[Bob Phillips]

Not surprised, I thought you woulkd have figured that yourself after the last one<G>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Thanx Bob, I unfortunately asked for the wrong thing. Let's try this again.
when closed!$A$S:$A$S=1 sum closed!$P$2:$P$19999 and divide by $P$4





Bob Phillips wrote:

=SUMPRODUCT((closed!$G$2:$G$19999<3)*( closed!$A$S:$A$S=1 ),(closed!$P$2:$P$19999))/$P$4
--
HTH Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Yes, I'm back!!
I could use some help with this averaging formula. I need it to now include only rows where closed!$A$S:$A$S=1

=SUMIF(closed!$G$2:$G$19999,"<3",closed!$P$2:$P$19999)/$P$4

thanx in advance!
~Julz