I have a SUMIF formula

SUMIF(U3:U3000,"<cleared",Q3:Q3000)

that sums a value stored in column Q items that do not have a status of
cleared (status is located in column U)

I want to add a second criteria to only sumif the above is true and i have
column L that has the number of days the invoice is past due - i only want to
include in the subtotal items that are 30 days or greater

Is this a sum product function and how would i write that?

One way

=SUMPRODUCT(--(U3:U3000<"cleared"),--(L3:L3000=30),Q3:Q3000)


--


Regards,


Peo Sjoblom


"Belinda7237" wrote in message
...
I have a SUMIF formula

SUMIF(U3:U3000,"<cleared",Q3:Q3000)

that sums a value stored in column Q items that do not have a status of
cleared (status is located in column U)

I want to add a second criteria to only sumif the above is true and i have
column L that has the number of days the invoice is past due - i only want
to
include in the subtotal items that are 30 days or greater

Is this a sum product function and how would i write that?

Try

=SUMPRODUCT((U3:U3000<"Cleared")*(L3:L3000<TODAY( )-30)*(Q3:Q3000))

"Belinda7237" wrote:

I have a SUMIF formula

SUMIF(U3:U3000,"<cleared",Q3:Q3000)

that sums a value stored in column Q items that do not have a status of
cleared (status is located in column U)

I want to add a second criteria to only sumif the above is true and i have
column L that has the number of days the invoice is past due - i only want to
include in the subtotal items that are 30 days or greater

Is this a sum product function and how would i write that?

=SUMPRODUCT(--(U3:U3000<"cleared"),--(L3:L3000=30),Q3:Q3000)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Belinda7237" wrote in message
...
I have a SUMIF formula

SUMIF(U3:U3000,"<cleared",Q3:Q3000)

that sums a value stored in column Q items that do not have a status of
cleared (status is located in column U)

I want to add a second criteria to only sumif the above is true and i have
column L that has the number of days the invoice is past due - i only want
to
include in the subtotal items that are 30 days or greater

Is this a sum product function and how would i write that?

Try this:

=SUMPRODUCT((U3:U3000<"cleared")*(L3:L300030)*(Q 3:Q3000))

Hope this helps.

Pete

On Jul 1, 7:07*pm, Belinda7237
wrote:
I have a SUMIF formula

SUMIF(U3:U3000,"<cleared",Q3:Q3000)

that sums a value stored in column Q items that do not have a status of
cleared (status is located in column U)

I want to add a second criteria to only sumif the above is true and i have
column L that has the number of days the invoice is past due - i only want to
include in the subtotal items that are 30 days or greater

Is this a sum product function and how would i write that?

Is this a sum product function and how would i write that?

SUMPRODUCT will do this but if you're using Excel 2007 then the SUMIFS
function would be a better choice.

=SUMIFS(Q3:Q3000,L3:L3000,"30",U3:U3000,"<cleare d")

SUMPRODUCT will work in any version from 97-2007

=SUMPRODUCT(--(L3:L30030),--(U3:U3000<"cleared"),Q3:Q3000)

Better to use cells to hold the criteria:

A1 = 30
B1 = cleared

=SUMIFS(Q3:Q3000,L3:L3000,""&A1,U3:U3000,"<"&B1)

=SUMPRODUCT(--(L3:L300A1),--(U3:U3000<B1),Q3:Q3000)


--
Biff
Microsoft Excel MVP


"Belinda7237" wrote in message
...
I have a SUMIF formula

SUMIF(U3:U3000,"<cleared",Q3:Q3000)

that sums a value stored in column Q items that do not have a status of
cleared (status is located in column U)

I want to add a second criteria to only sumif the above is true and i have
column L that has the number of days the invoice is past due - i only want
to
include in the subtotal items that are 30 days or greater

Is this a sum product function and how would i write that?

hi Belinda
Try
SUMPRODUCT((U3:U3000,"<cleared")*(L3:L3000=30)*Q 3:Q3000)


--
Regards
Roger Govier

"Belinda7237" wrote in message
...
I have a SUMIF formula

SUMIF(U3:U3000,"<cleared",Q3:Q3000)

that sums a value stored in column Q items that do not have a status of
cleared (status is located in column U)

I want to add a second criteria to only sumif the above is true and i have
column L that has the number of days the invoice is past due - i only want
to
include in the subtotal items that are 30 days or greater

Is this a sum product function and how would i write that?

Ooops! 2 typos and I missed this:

30 days or greater

So, everywhere I have 30 just change to =30

L3:L300 in 2 places should be L3:L3000

I wonder if it's gonna be one of those days!


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Is this a sum product function and how would i write that?

SUMPRODUCT will do this but if you're using Excel 2007 then the SUMIFS
function would be a better choice.

=SUMIFS(Q3:Q3000,L3:L3000,"30",U3:U3000,"<cleare d")

SUMPRODUCT will work in any version from 97-2007

=SUMPRODUCT(--(L3:L30030),--(U3:U3000<"cleared"),Q3:Q3000)

Better to use cells to hold the criteria:

A1 = 30
B1 = cleared

=SUMIFS(Q3:Q3000,L3:L3000,""&A1,U3:U3000,"<"&B1)

=SUMPRODUCT(--(L3:L300A1),--(U3:U3000<B1),Q3:Q3000)


--
Biff
Microsoft Excel MVP


"Belinda7237" wrote in message
...
I have a SUMIF formula

SUMIF(U3:U3000,"<cleared",Q3:Q3000)

that sums a value stored in column Q items that do not have a status of
cleared (status is located in column U)

I want to add a second criteria to only sumif the above is true and i
have
column L that has the number of days the invoice is past due - i only
want to
include in the subtotal items that are 30 days or greater

Is this a sum product function and how would i write that?

thanks to all of you - works perfectly!

"Belinda7237" wrote:

I have a SUMIF formula

SUMIF(U3:U3000,"<cleared",Q3:Q3000)

that sums a value stored in column Q items that do not have a status of
cleared (status is located in column U)

I want to add a second criteria to only sumif the above is true and i have
column L that has the number of days the invoice is past due - i only want to
include in the subtotal items that are 30 days or greater

Is this a sum product function and how would i write that?