Sum macro for dynamic range

[western93]

I am trying to find a way to setup a macro to sum a range of selected cells in col c when column a condition is true and column b condition is true. The range of the cells changes each day depending if the bin#(column b) had activity or not. (One day the range for zone4 bins400 may start in E34 and the next day in E50)

I need 4 totals:

zone4 bins 400- 416
zone4 bins 417- 432
zone5 bins 500- 519
zone5 bins 520- 530


Thanks in advance for your help!

 

[KenWright]

So give us an example of your data. What sort of ranges are we talking here and where is your data located, eg does this table start in say E1 and then carry on down with no blanks in the data until the table finishes (albeit the range may change daily)?

Regards
Ken..................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[western93]

The first zone value always starts in cell A2 and there is never any blanks in the data. The selected bin counts are totaled in column E for the last bin# nbr for the desired zone/bin.

zone bin count sum
0 0 1,938
1 100 185
1 102 186
1 103 105
1 106 77
1 107 70
1 108 207
1 109 145
4 400 11
4 401 27
4 402 92
4 403 24
4 416 48 202
58 520 23
58 521 29
58 522 26
60 600 17
60 601 21

 

[bluedragon2]

SUMPRODUCT((A1:A1000=4)*(C1:C1000))

or

Subtotals

or

Pivot Table



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

Pivot table with a dynamic range or a SUMPRODUCT formula would be my preferences

SUMPRODUCT((B1:B1000>=400)*(B1:B1000<=416)*(C1:C1000))
SUMPRODUCT((B1:B1000>=417)*(B1:B1000<=432)*(C1:C1000))
SUMPRODUCT((B1:B1000>=500)*(B1:B1000<=519)*(C1:C1000))
SUMPRODUCT((B1:B1000>=520)*(B1:B1000<=530)*(C1:C1000))

Regards
Ken................




----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[KenWright]

Actually better make those 1s into 2s else it will bomb out if you have text headers.

If you want dynamic arrays

=SUMPRODUCT((OFFSET($B$2,,,COUNTA($B:$B)-1)>=400)*(OFFSET($B$2,,,COUNTA($B:$B)-1)<=416)*(OFFSET($C$2,,,COUNTA($C:$C)-1)))

(Again assuming text headers in row 1)

and so on..

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------