Summarizing Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

krc547 - 31 Mar 2008 18:34 GMT

Is there a way I can have a macro look at a worksheet and look down a certain
column, if the number in that column is x then return the value of a certain
column.
For example:

Summary worksheet macro look at jan 08 worksheet. Then go down column "C"
and if they is a 0,1,2,3,4 then grab the amount in column "G" and continue to
add the amounts together when at the end of the worksheet place the total in
the summary worksheet Column "C" cell 5.

Reply to this Message

Pete_UK - 31 Mar 2008 19:13 GMT

Try this formula (NOT a macro) in C5 of your summary sheet:

=SUMPRODUCT(('Jan 08'!C1:C1000>=0)*('Jan 08'!C1:C1000<=5)*('Jan 08'!
G1:G1000))

I've assumed 1000 rows - adjust to suit, but you can't use complete
column references prior to XL2007.

Hope this helps.

Pete

> Is there a way I can have a macro look at a worksheet and look down a certain
> column, if the number in that column is x then return the value of a certain
[quoted text clipped - 5 lines]
> add the amounts together when at the end of the worksheet place the total in
> the summary worksheet Column "C" cell 5.

Reply to this Message