Hi, All:


What I want to accomplish is that I want to summarize the data in the
sheets "data" under some conditions?

For example I want to know how many customers have balance below 4000
and how many of them have balance above 5000, etc

So I have a set of conditions in column B in "summary" sheet, < 4000,

5000

in column C, another variable =0, =1 etc

...........

In column O, is the sum of customers satisfy my conditions in column B
to P.

So I have B2="'< 4000," B3="5000".......

How can I reference them in VBA, for example, I want to check if
Sheets("data").cells(1,1)0
or not. I want to use B2 directly. How can I do that in VBA
I tried

if Sheets("data").cells(1,1) & Sheets("summary").(2, 2) then
..........
end if

It doesn't work. Any one knows how to handle this? Thanks.

You can access worksheet functions from VBA as follows

MyTotal = worksheetfunctions.sum("A1:B10")

what is also nice is that any range in VBA can also be passed to the
worksheet functions

set MyRange = sheets("Sheet5").Range("B11:C12")
MyTotal = worksheetfunctions.sum(MyRange)

The range can also be a name from the worksheets
MyTotal = worksheetfunctions.sum(activesheet.MyRange)

You probably want to use the worksheet function SUMIF.
set MyRange = sheets("Sheet5").Range("A11:A100")
lesstotal = worksheetfunctions. _
sumif(MyRange,"'< 4000",MyRange.offset(rowoffset:=0,columnoffset:=1)
sheets("Summary").Range("B1") = lesstotal


Another method is for VBA to actuall put the formula for sumif into tyour
summary worksheet
lesstotalformula = "=sumif(Sheet5!A11:A100,""<4000"",Sheet5!B11:B100) "
sheets("Summary").Range("B1").formula = lesstotalformula

the above can be made dynamic as follows

LastRow = 100
lesstotalformula = "=sumif(Sheet5!A11:A" & Lastrow & _
",""<4000"",Sheet5!B11:B" & LastRow & ")"
sheets("Summary").Range("B1").formula = lesstotalformula


" wrote:

Hi, All:


What I want to accomplish is that I want to summarize the data in the
sheets "data" under some conditions?

For example I want to know how many customers have balance below 4000
and how many of them have balance above 5000, etc

So I have a set of conditions in column B in "summary" sheet, < 4000,

5000

in column C, another variable =0, =1 etc

...........

In column O, is the sum of customers satisfy my conditions in column B
to P.

So I have B2="'< 4000," B3="5000".......

How can I reference them in VBA, for example, I want to check if
Sheets("data").cells(1,1)0
or not. I want to use B2 directly. How can I do that in VBA
I tried

if Sheets("data").cells(1,1) & Sheets("summary").(2, 2) then
..........
end if

It doesn't work. Any one knows how to handle this? Thanks.

You might try looking at Advanced Filter. In the cell above your balances
enter "Balances" for one of the columns in your List range.

For your Criteria range, in say F1 & G1 enter "Balances" in both cells
F2: <5000
G3: 6000

Note not in same rows for 'Or' conditions.

See this excellent introduction to Advanced Filters by Debra Dalgleish
http://www.contextures.com/xladvfilter01.html

Try recording a macro

Regards
Peter T


wrote in message
ups.com...
Hi, All:


What I want to accomplish is that I want to summarize the data in the
sheets "data" under some conditions?

For example I want to know how many customers have balance below 4000
and how many of them have balance above 5000, etc

So I have a set of conditions in column B in "summary" sheet, < 4000,

5000

in column C, another variable =0, =1 etc

..........

In column O, is the sum of customers satisfy my conditions in column B
to P.

So I have B2="'< 4000," B3="5000".......

How can I reference them in VBA, for example, I want to check if
Sheets("data").cells(1,1)0
or not. I want to use B2 directly. How can I do that in VBA
I tried

if Sheets("data").cells(1,1) & Sheets("summary").(2, 2) then
.........
end if

It doesn't work. Any one knows how to handle this? Thanks.