Sum Value If Corresponding Column Meet Criteria

P

PGiessler

What I am attempting to do is look in Column "S" and sum only those values where Column "T" is less than or equal to the value in cell E23.

=SUMPRODUCT(S53:S5054,(If(t53:5054>=e23))) I know this doesn't work, and I am going goofy trying to figure it out. Any help would be appreciated.

Column S = dollar value of outstanding invoices
Column T = date invoice was processed
Cell E23 = Last day of the Quarter (i.e. 38168)

In the destination cell I want the dollar value sum of all invoices process before the end of the quarter.
 
E

Earl Kiosterud

P,

=SUMPRODUCT((S2:S65536)*(T2:T65536<=$E$23))
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

PGiessler said:
What I am attempting to do is look in Column "S" and sum only those values
Click to expand...
where Column "T" is less than or equal to the value in cell E23.
=SUMPRODUCT(S53:S5054,(If(t53:5054>=e23))) I know this doesn't work, and I
Click to expand...
am going goofy trying to figure it out. Any help would be appreciated.
Column S = dollar value of outstanding invoices
Column T = date invoice was processed
Cell E23 = Last day of the Quarter (i.e. 38168)

In the destination cell I want the dollar value sum of all invoices
Click to expand...
process before the end of the quarter.
 
B

Bob Phillips

Try this

=SUMPRODUCT(--(T53:T5054>=E23),(S53:S5054))

--

HTH

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

PGiessler said:
What I am attempting to do is look in Column "S" and sum only those values
Click to expand...
where Column "T" is less than or equal to the value in cell E23.
=SUMPRODUCT(S53:S5054,(If(t53:5054>=e23))) I know this doesn't work, and I
Click to expand...
am going goofy trying to figure it out. Any help would be appreciated.
Column S = dollar value of outstanding invoices
Column T = date invoice was processed
Cell E23 = Last day of the Quarter (i.e. 38168)

In the destination cell I want the dollar value sum of all invoices
Click to expand...
process before the end of the quarter.
 
P

PGiessler

Thanks -- Works like a charm! And, if I understand it correctly the double dash renders the array statement a True/False result. Then for every True the SumProduct function kicks in.

Great Stuff!

Paul
 
P

Peo Sjoblom

This is probably a little bit more efficient


=SUMIF(T53:T5054,">="&E23,S53:S5054)

also in the sumproduct formula

T53:T5054>=E23

renders the TRUE,False while the unary minuses turns it into 1s and 0s

note that is you want something like
=E23 <=E24 then the sumproduct formula is probably a bit more easy to use
Click to expand...

=SUMPRODUCT(--(T53:T5054>=E23),--(T53:T5054<=E24),S53:S5054)

compared to

=SUMIF(T53:T5054,">="&E23,S53:S5054)-SUMIF(T53:T5054,">"&E24,S53:S5054)



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



PGiessler said:
Thanks -- Works like a charm! And, if I understand it correctly the double
Click to expand...
dash renders the array statement a True/False result. Then for every True
the SumProduct function kicks in.
 
B

Bob Phillips

Not quite. Take a look at

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Multiple Condition Tests (using SUMPRODUCT)

for a detailed explanation

--

HTH

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

PGiessler said:
Thanks -- Works like a charm! And, if I understand it correctly the double
Click to expand...
dash renders the array statement a True/False result. Then for every True
the SumProduct function kicks in.
 
Top