| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba and conditional totalling ... WorksheetFunction SumIf ?
I am not sure if SumIf is the correct function to use ....
I have a worksheet containing the following Column A = Date Column D = Amount (+ve and -ve values to represent Debits and Credits) Column F = Status ("Cleared" or "Not Cleared") I would like to display, on a userform, various totals e.g. Between a range of values in column A, the sum of negative values in Column D that have the status "Cleared" in Column F. If you could let me have the code for this calculation, hopefully, I can edit it to produce similar sub totals Regards and TIA |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vba and conditional totalling ... WorksheetFunction SumIf ?
=SUMPRODUCT(--(A2:A100=--"2005-06-01"),--(A2:A100<=--"2005-06-30"), --(F2:F
100="Cleared"),D2:D100) which gives total amount for Cleared items in June. To do this in VBA, use myTot = evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3 0""), --(F2:F100=""Cleared""),D2:D100)") -- HTH RP (remove nothere from the email address if mailing direct) "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... I am not sure if SumIf is the correct function to use .... I have a worksheet containing the following Column A = Date Column D = Amount (+ve and -ve values to represent Debits and Credits) Column F = Status ("Cleared" or "Not Cleared") I would like to display, on a userform, various totals e.g. Between a range of values in column A, the sum of negative values in Column D that have the status "Cleared" in Column F. If you could let me have the code for this calculation, hopefully, I can edit it to produce similar sub totals Regards and TIA |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vba and conditional totalling ... WorksheetFunction SumIf ?
Bob,
Many thanks for the swift response, What you suggested is a start .... but it evaluates the total of all cleared payments (both +ve and -ve). Would you mind editing it to produce the sum of either +ve or -ve ... I'm not sure I am upto it. Regards ..................................... "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A100=--"2005-06-01"),--(A2:A100<=--"2005-06-30"), --(F2:F 100="Cleared"),D2:D100) which gives total amount for Cleared items in June. To do this in VBA, use myTot = evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3 0""), --(F2:F100=""Cleared""),D2:D100)") -- HTH RP (remove nothere from the email address if mailing direct) "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... I am not sure if SumIf is the correct function to use .... I have a worksheet containing the following Column A = Date Column D = Amount (+ve and -ve values to represent Debits and Credits) Column F = Status ("Cleared" or "Not Cleared") I would like to display, on a userform, various totals e.g. Between a range of values in column A, the sum of negative values in Column D that have the status "Cleared" in Column F. If you could let me have the code for this calculation, hopefully, I can edit it to produce similar sub totals Regards and TIA |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vba and conditional totalling ... WorksheetFunction SumIf ?
=SUMPRODUCT(--(A2:A100=--"2005-06-01"),--(A2:A100<=--"2005-06-30"), --(F2:F
100="Cleared"),--(D2:D1000),D2:D100) myTot = evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3 0""), --(F2:F100=""Cleared""),--(""D2:D100""0),D2:D100)") I'll leave negative numbers for you -- HTH RP (remove nothere from the email address if mailing direct) "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... Bob, Many thanks for the swift response, What you suggested is a start .... but it evaluates the total of all cleared payments (both +ve and -ve). Would you mind editing it to produce the sum of either +ve or -ve ... I'm not sure I am upto it. Regards .................................... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vba and conditional totalling ... WorksheetFunction SumIf ?
Thanks Bob ...That is just what I needed and although I cannot fully
comprehend the syntax I did find a small error which I corrected i.e. I removed the double quotes from .... (""D2:D100""0) Many thanks ...... what a tremendous resource the newsgroup is "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A100=--"2005-06-01"),--(A2:A100<=--"2005-06-30"), --(F2:F 100="Cleared"),--(D2:D1000),D2:D100) myTot = evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3 0""), --(F2:F100=""Cleared""),--(""D2:D100""0),D2:D100)") I'll leave negative numbers for you -- HTH RP (remove nothere from the email address if mailing direct) "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... Bob, Many thanks for the swift response, What you suggested is a start .... but it evaluates the total of all cleared payments (both +ve and -ve). Would you mind editing it to produce the sum of either +ve or -ve ... I'm not sure I am upto it. Regards .................................... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vba and conditional totalling ... WorksheetFunction SumIf ?
Mea culpa! Well spotted.
Bob "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... Thanks Bob ...That is just what I needed and although I cannot fully comprehend the syntax I did find a small error which I corrected i.e. I removed the double quotes from .... (""D2:D100""0) Many thanks ...... what a tremendous resource the newsgroup is "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A100=--"2005-06-01"),--(A2:A100<=--"2005-06-30"), --(F2:F 100="Cleared"),--(D2:D1000),D2:D100) myTot = evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3 0""), --(F2:F100=""Cleared""),--(""D2:D100""0),D2:D100)") I'll leave negative numbers for you -- HTH RP (remove nothere from the email address if mailing direct) "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... Bob, Many thanks for the swift response, What you suggested is a start .... but it evaluates the total of all cleared payments (both +ve and -ve). Would you mind editing it to produce the sum of either +ve or -ve ... I'm not sure I am upto it. Regards .................................... |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vba and conditional totalling ... WorksheetFunction SumIf ?
hi,
the formula works with only 1 argument for the date - A2:A100=--"6/01/2005" - i cant put in the second part. could you please post the function as you have it again. Thanks, Alex "SA3214 @Eclipse.co.uk" wrote: Bob, Many thanks for the swift response, What you suggested is a start .... but it evaluates the total of all cleared payments (both +ve and -ve). Would you mind editing it to produce the sum of either +ve or -ve ... I'm not sure I am upto it. Regards ..................................... "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A100=--"2005-06-01"),--(A2:A100<=--"2005-06-30"), --(F2:F 100="Cleared"),D2:D100) which gives total amount for Cleared items in June. To do this in VBA, use myTot = evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3 0""), --(F2:F100=""Cleared""),D2:D100)") -- HTH RP (remove nothere from the email address if mailing direct) "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... I am not sure if SumIf is the correct function to use .... I have a worksheet containing the following Column A = Date Column D = Amount (+ve and -ve values to represent Debits and Credits) Column F = Status ("Cleared" or "Not Cleared") I would like to display, on a userform, various totals e.g. Between a range of values in column A, the sum of negative values in Column D that have the status "Cleared" in Column F. If you could let me have the code for this calculation, hopefully, I can edit it to produce similar sub totals Regards and TIA |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vba and conditional totalling ... WorksheetFunction SumIf ?
Alex ... this problem was resolved in the later threads entitled:
'Ping ... Bob Philips' started on 17/06/2005 'Ping Bernie (or any other sage)' started on 18/06/2005 Regards Jim Burton (aka Jimbo and SA3214) --------------------------------------------------[ "alex" wrote in message ... hi, the formula works with only 1 argument for the date - A2:A100=--"6/01/2005" - i cant put in the second part. could you please post the function as you have it again. Thanks, Alex "SA3214 @Eclipse.co.uk" wrote: Bob, Many thanks for the swift response, What you suggested is a start .... but it evaluates the total of all cleared payments (both +ve and -ve). Would you mind editing it to produce the sum of either +ve or -ve ... I'm not sure I am upto it. Regards ..................................... "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A100=--"2005-06-01"),--(A2:A100<=--"2005-06-30"), --(F2:F 100="Cleared"),D2:D100) which gives total amount for Cleared items in June. To do this in VBA, use myTot = evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3 0""), --(F2:F100=""Cleared""),D2:D100)") -- HTH RP (remove nothere from the email address if mailing direct) "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... I am not sure if SumIf is the correct function to use .... I have a worksheet containing the following Column A = Date Column D = Amount (+ve and -ve values to represent Debits and Credits) Column F = Status ("Cleared" or "Not Cleared") I would like to display, on a userform, various totals e.g. Between a range of values in column A, the sum of negative values in Column D that have the status "Cleared" in Column F. If you could let me have the code for this calculation, hopefully, I can edit it to produce similar sub totals Regards and TIA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional sumif? | Excel Discussion (Misc queries) | |||
| Conditional sumif | Excel Discussion (Misc queries) | |||
| Conditional SUMIF | Excel Worksheet Functions | |||
| WorkSheetFunction.CountIf & WorkSheetFunction.SumIf with 2 conditions? | Excel Programming | |||
| WorksheetFunction.CountIf & Worksheet.SumIf with 2 conditions? | Excel Programming | |||
Linear Mode
