1. Registered User
Join Date
Feb 2010
Posts
22

Hi,

In an excel sheet I have data as shown below:
Code:
```Code No.	BillDate	Cost
A00001		14/05/05	435.00
A00002		14/05/05	40.00
A00003		16/05/05	20.00
A00004		14/05/05	435.00
A00005		12/05/05	945.00
A00006		12/05/05	789.00```
There are several number of records.

There is an userform, on which user enters two dates & on click of a button, it should display the Sum of cost between these 2 dates:

Suppose user enters 13/05/05 & 14/05/05, it should display 910.00

Thanks.

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

In XL2003 or earlier, you can call the Application.WorksheetFunction.SumIf() method (twice) to calculate the relevant sum.
In XL2007 or later, you can call Application.WorksheetFunction.SumIfs() .

If an example would help let me know which version of XL you are using.
Last edited by Colin Legg; 02-12-10 at 10:43.

3. Registered User
Join Date
Feb 2010
Posts
22
I use XL 2003, appreciate if you could provide an example.

4. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Okay, let's suppose the data in post#1 is in range A1:C7.

To get the cost on 13/05/05 and 14/05/05, if we were to use a worksheet formula , it would be something like this:
=SUMIF(B2:B7,">=38485",C2:C7)-SUMIF(B2:B7,">38486",C2:C7)

To emulate this in VBA we can use something like the following (of course there are many different ways you can skin a cat). You just have to be a bit careful with UK/American date formats (use VBA.DateValue to get the dates from the textboxes which hold string values) and convert them into doubles (VBA.CDbl) for the SUMIF() calls. This is just a basic template - no error handling or defensive coding included.
Code:
```Private Sub CommandButton1_Click()
Dim StartDate As Date, EndDate As Date
Dim rngDates As Range, rngCosts As Range
Dim dblSum As Double

StartDate = DateValue(TextBox1.Text)
EndDate = DateValue(TextBox2.Text)

Set rngDates = Sheet1.Range("B2:B7")
Set rngCosts = Sheet1.Range("C2:C7")

With Application.WorksheetFunction
dblSum = .SumIf(rngDates, ">=" & CDbl(StartDate), rngCosts) - .SumIf(rngDates, ">" & CDbl(EndDate), rngCosts)
End With

MsgBox CStr(dblSum)

End Sub```

5. Registered User
Join Date
Feb 2010
Posts
22
Thanks!

That helped me!

6. Registered User
Join Date
Feb 2010
Posts
22
There is another requirement:

For the below data,
a) I need to have the COUNT as well as SUM of Cost, whereever the SoldDate is empty
b) From startDate to endDate (as mentioned in your code) of SoldDate, I need the SUM of Cost, whereever SoldBill doesn't begin with 'R' or 'D'.

Code:
```Code No.	BillDate	Cost	SoldDate	SoldBill
A00001		14/05/05	435.00	15/05/05	1234
A00002		14/05/05	40.00
A00003		16/05/05	20.00	16/05/05	R123
A00004		14/05/05	435.00
A00005		12/05/05	945.00	14/05/05	D123
A00006		12/05/05	789.00	14/05/05	234```
For this case, Ouput would be:
a) 2 and 475.00
b) For dates 15/05/05 and 16/05/05, 435.00
For dates 14/05/05 and 16/05/05, 1224.00

7. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
There are many ways of doing these.

a) I need to have the COUNT as well as SUM of Cost, whereever the SoldDate is empty
The formula for each would be:
=COUNTIF(D2:D7,"=")
=SUMIF(D2:D7,"=",C2:C7)

The COUNTIF() formula literally counts empty cells, so it assumes that D2:D7 is the exact range. If the requirement is really that you want the sum and count whenever SoldDate is empty AND BillDate is not empty then, in XL 03, you could use SUMPRODUCT instead:
=SUMPRODUCT(--(ISNUMBER(B2:B7)),--(D2:D7=""))
=SUMPRODUCT(--(ISNUMBER(B2:B7)),--(D2:D7=""),C2:C7)

To emulate the COUNTIF() and SUMIF() examples in VBA, you can call the COUNTIF and SUMIF functions via the worksheetfunction object as demonstrated earlier in the thread.

To emulate the more complicated SUMPRODUCT() expressions these in VBA you can use the worksheet object's evaluate method, for example:
Code:
```Sub foo()

Debug.Print CStr(Sheet2.Evaluate("SUMPRODUCT(--(ISNUMBER(B2:B7)),--(D2:D7=""""),C2:C7)"))

End Sub```
b) From startDate to endDate (as mentioned in your code) of SoldDate, I need the SUM of Cost, whereever SoldBill doesn't begin with 'R' or 'D'.
Here's one formula approach which gives a result of 435:
=SUMPRODUCT(--(LEFT(E2:E7,1)<>"R"),--(LEFT(E2:E7,1)<>"D"),--(D2:D7>=DATE(2005,5,15)),--(D2:D7<=DATE(2005,5,16)),C2:C7)

Again, you can emulate in VBA via the Evaluate method:
Code:
```Sub foo()

Debug.Print CStr(Sheet2.Evaluate("SUMPRODUCT(--(LEFT(E2:E7,1)<>""R"")," & _
"--(LEFT(E2:E7,1)<>""D""),--(D2:D7>=DATE(2005,5,15))," & _
"--(D2:D7<=DATE(2005,5,16)),C2:C7)"))

End Sub```
Hope that helps...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•