Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif
I have a spreadsheet with a list of invoice dates and the amounts due. At
the bottom, I have due dates a week apart 08/03/07 08/10/07 08/17/07 08/24/07 08/31/07 If the invoice date is equal to or less than 08/10/07 and greater than 08/03/07, I would like to include it in my SUMIF. Everything I have tried (sumif and sumproduct) have produced $0 values. Here is an example of the latest formula I've found here on the discussion boards that I've tried to use. =SUMPRODUCT((A4:A340C344)*(A4:A340<=C345)*F4:F340 ) Help, please. kelly :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif
The formula does work so there can be either of these 2, the dates are seen
as text by Excel or the values in F4:F340 are text Your formula can be rewritten as =SUMPRODUCT(--(A4:A340I1),--(A4:A340<=J1),F4:F340) however I just tested your formula and it does what it should do Test for text as follows =ISTEXT(A4) copy down if you get and TRUE those values are text, do the same for the F column -- Regards, Peo Sjoblom "Kelly" wrote in message ... I have a spreadsheet with a list of invoice dates and the amounts due. At the bottom, I have due dates a week apart 08/03/07 08/10/07 08/17/07 08/24/07 08/31/07 If the invoice date is equal to or less than 08/10/07 and greater than 08/03/07, I would like to include it in my SUMIF. Everything I have tried (sumif and sumproduct) have produced $0 values. Here is an example of the latest formula I've found here on the discussion boards that I've tried to use. =SUMPRODUCT((A4:A340C344)*(A4:A340<=C345)*F4:F340 ) Help, please. kelly :) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif
thank you very very much!!
"Peo Sjoblom" wrote: The formula does work so there can be either of these 2, the dates are seen as text by Excel or the values in F4:F340 are text Your formula can be rewritten as =SUMPRODUCT(--(A4:A340I1),--(A4:A340<=J1),F4:F340) however I just tested your formula and it does what it should do Test for text as follows =ISTEXT(A4) copy down if you get and TRUE those values are text, do the same for the F column -- Regards, Peo Sjoblom "Kelly" wrote in message ... I have a spreadsheet with a list of invoice dates and the amounts due. At the bottom, I have due dates a week apart 08/03/07 08/10/07 08/17/07 08/24/07 08/31/07 If the invoice date is equal to or less than 08/10/07 and greater than 08/03/07, I would like to include it in my SUMIF. Everything I have tried (sumif and sumproduct) have produced $0 values. Here is an example of the latest formula I've found here on the discussion boards that I've tried to use. =SUMPRODUCT((A4:A340C344)*(A4:A340<=C345)*F4:F340 ) Help, please. kelly :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Sumif | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |