vba and conditional totalling ... WorksheetFunction SumIf ?

[SA3214]

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

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A100>=--"2005-06-01"),--(A2:A100<=--"2005-06-30"), --(F2:F
100="Cleared"),D2100)

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""),D2100)")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[SA3214]

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]

=SUMPRODUCT(--(A2:A100>=--"2005-06-01"),--(A2:A100<=--"2005-06-30"), --(F2:F
100="Cleared"),--(D2100>0),D2100)

myTot =
evaluate("SUMPRODUCT(--(A2:A100>=--""2005-06-01""),--(A2:A100<=--""2005-06-3
0""), --(F2:F100=""Cleared""),--(""D2100"">0),D2100)")

I'll leave negative numbers for you


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[SA3214]

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 .... (""D2100"">0)

Many thanks ...... what a tremendous resource the newsgroup is

 

[Bob Phillips]

Mea culpa! Well spotted.

Bob

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 .... (""D2100"">0)

Many thanks ...... what a tremendous resource the newsgroup is

 

[Guest]

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]

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)
--------------------------------------------------[