lookup?

[Khalil Handal]

Hi,

I have 13 worksheets (12 months, summary), for each month I have 1 column
for item (B) and 1 column for codes for each item (EI). Item start at row 7.

The item codes are: 100, 200, 300, . 900. Sub items have the codes like 710,
720 or 510, 520, not all of them have sub items code.

Column C, D contains currency. See table below:

A B C D
EI
Date Particulars
Code
10/01/05 Food 38.00 0.00 710
10/01/05 Food 16.00 0.00 710
10/01/05 Supply 50.00 0.00 800
10/01/05 Books 48.00 20.00 710
10/01/05 S. Supply 812.00 0.00 820
01/01/05 Equipment 156.00 0.00 500
10/01/05 Garden 110.00 0.00 500

The summary sheet - in the same work book - looks like this:
(code 710 is an example)
Code October November December ...
all 2 months
710 (38+0+16+0+48+20)
200
210
220
500

I need to lookup the codes and sum the amounts in column C and D for that
code item and put it in the row of October in summary sheet. The same for
the rest
of the months.

Note: the number of particulars differs from one month to another.
one month 10 rows another 20 rows depending on expences.

Help is appreciated

Khalil Handal

[Peo Sjoblom]

Put all the sheet names you want to calculate in a contiguous range (in this
example H1:H12)
then you can use

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H12&"'!E:E"),710,INDIRECT("'"&H1:H12&"'!C:C")))+SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H12&"'!E:E"),710,INDIRECT("'"&H1:H12&"'!D:D")))

replace 710 with a cell where you put the different code numbers

this assumes that the amounts are in column C and D and the codes in E

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Khalil Handal" <khhandal@stthom.edu> wrote in message
news:OUXI3H4SGHA.5728@tk2msftngp13.phx.gbl...
> Hi,
>
> I have 13 worksheets (12 months, summary), for each month I have 1 column
> for item (B) and 1 column for codes for each item (EI). Item start at row
> 7.
>
> The item codes are: 100, 200, 300, . 900. Sub items have the codes like
> 710,
> 720 or 510, 520, not all of them have sub items code.
>
> Column C, D contains currency. See table below:
>
> A B C D EI
> Date Particulars Code
> 10/01/05 Food 38.00 0.00 710
> 10/01/05 Food 16.00 0.00 710
> 10/01/05 Supply 50.00 0.00 800
> 10/01/05 Books 48.00 20.00 710
> 10/01/05 S. Supply 812.00 0.00 820
> 01/01/05 Equipment 156.00 0.00 500
> 10/01/05 Garden 110.00 0.00 500
>
> The summary sheet - in the same work book - looks like this:
> (code 710 is an example)
> Code October November December
> ... all 2 months
> 710 (38+0+16+0+48+20)
> 200
> 210
> 220
> 500
>
> I need to lookup the codes and sum the amounts in column C and D for that
> code item and put it in the row of October in summary sheet. The same for
> the rest
> of the months.
>
> Note: the number of particulars differs from one month to another.
> one month 10 rows another 20 rows depending on expences.
>
> Help is appreciated
>
> Khalil Handal
>
>

[Bill Kuunders]

enter this formula on a new sheet (trial balance) in cell D4

=SUM(IF(payments_code=B4,net_payments,0))+SUM(IF(payments_code=B4,journal,0))
entered as an array formula....hold down "cntr" and "shift" buttons when
pushing "enter"
this will put {.....} brackets around the formula

you may want to change the names but in priciple it will add all the
net_payments if the payments_code is equal to the value in cell B4 and the
journal if the the payments_code is equal to B4

using names for your ranges makes it easier to maintain the sheet later when
you have to increase ranges.

payments_ code is a name for the range with the codes EI7:EI100
net_payments is a name for the range C7:C100
journal is a name for the range D7:D100

The cell B4 holds the code you want to add (on the trial balance sheet for
ease of maintenance)
B4 being the first code of your trial balance........C4 would be the
description


you can extend the formula down the column as far as necessary
with the b column holding all you codes.

Good luck.


--
Greetings from New Zealand
Bill K


"Khalil Handal" <khhandal@stthom.edu> wrote in message
news:OUXI3H4SGHA.5728@tk2msftngp13.phx.gbl...
> Hi,
>
> I have 13 worksheets (12 months, summary), for each month I have 1 column
> for item (B) and 1 column for codes for each item (EI). Item start at row
> 7.
>
> The item codes are: 100, 200, 300, . 900. Sub items have the codes like
> 710,
> 720 or 510, 520, not all of them have sub items code.
>
> Column C, D contains currency. See table below:
>
> A B C D EI
> Date Particulars Code
> 10/01/05 Food 38.00 0.00 710
> 10/01/05 Food 16.00 0.00 710
> 10/01/05 Supply 50.00 0.00 800
> 10/01/05 Books 48.00 20.00 710
> 10/01/05 S. Supply 812.00 0.00 820
> 01/01/05 Equipment 156.00 0.00 500
> 10/01/05 Garden 110.00 0.00 500
>
> The summary sheet - in the same work book - looks like this:
> (code 710 is an example)
> Code October November December
> ... all 2 months
> 710 (38+0+16+0+48+20)
> 200
> 210
> 220
> 500
>
> I need to lookup the codes and sum the amounts in column C and D for that
> code item and put it in the row of October in summary sheet. The same for
> the rest
> of the months.
>
> Note: the number of particulars differs from one month to another.
> one month 10 rows another 20 rows depending on expences.
>
> Help is appreciated
>
> Khalil Handal
>
>

[Bill Kuunders]

I forgot that you already have 12 seperate sheets
You could also set up a summary sheet which will pick up each month seperate
using a formula similar to this

=SUM(IF(expense_code=B4,IF(datesexpenses=J$2,netexpenses,0),0))+SUM(IF(expense_code=B4,IF(datesexpenses=J$2,journal,0),0))
again entered as an array formula and you would have one of these for each
month.

with J2 being the cell holding the month
and datesexpenses being a column in the expenses sheet which has the date in
the format of "mmm"
using the formula =TEXT(C20,"MMM")

this would give you the opportunity to hold one yearly expense sheet rather
that 12 seperate sheets

again
have fun
--
Greetings from New Zealand
Bill K

"Bill Kuunders" <bill.kuunders@xtra.co.nz> wrote in message
news:OKqXjR5SGHA.5496@TK2MSFTNGP11.phx.gbl...
> enter this formula on a new sheet (trial balance) in cell D4
>
> =SUM(IF(payments_code=B4,net_payments,0))+SUM(IF(payments_code=B4,journal,0))
> entered as an array formula....hold down "cntr" and "shift" buttons when
> pushing "enter"
> this will put {.....} brackets around the formula
>
> you may want to change the names but in priciple it will add all the
> net_payments if the payments_code is equal to the value in cell B4 and the
> journal if the the payments_code is equal to B4
>
> using names for your ranges makes it easier to maintain the sheet later
> when you have to increase ranges.
>
> payments_ code is a name for the range with the codes EI7:EI100
> net_payments is a name for the range C7:C100
> journal is a name for the range D7:D100
>
> The cell B4 holds the code you want to add (on the trial balance sheet for
> ease of maintenance)
> B4 being the first code of your trial balance........C4 would be the
> description
>
>
> you can extend the formula down the column as far as necessary
> with the b column holding all you codes.
>
> Good luck.
>
>
> --
> Greetings from New Zealand
> Bill K
>
>
> "Khalil Handal" <khhandal@stthom.edu> wrote in message
> news:OUXI3H4SGHA.5728@tk2msftngp13.phx.gbl...
>> Hi,
>>
>> I have 13 worksheets (12 months, summary), for each month I have 1 column
>> for item (B) and 1 column for codes for each item (EI). Item start at row
>> 7.
>>
>> The item codes are: 100, 200, 300, . 900. Sub items have the codes like
>> 710,
>> 720 or 510, 520, not all of them have sub items code.
>>
>> Column C, D contains currency. See table below:
>>
>> A B C D EI
>> Date Particulars Code
>> 10/01/05 Food 38.00 0.00 710
>> 10/01/05 Food 16.00 0.00 710
>> 10/01/05 Supply 50.00 0.00 800
>> 10/01/05 Books 48.00 20.00 710
>> 10/01/05 S. Supply 812.00 0.00 820
>> 01/01/05 Equipment 156.00 0.00 500
>> 10/01/05 Garden 110.00 0.00 500
>>
>> The summary sheet - in the same work book - looks like this:
>> (code 710 is an example)
>> Code October November December
>> ... all 2 months
>> 710 (38+0+16+0+48+20)
>> 200
>> 210
>> 220
>> 500
>>
>> I need to lookup the codes and sum the amounts in column C and D for that
>> code item and put it in the row of October in summary sheet. The same for
>> the rest
>> of the months.
>>
>> Note: the number of particulars differs from one month to another.
>> one month 10 rows another 20 rows depending on expences.
>>
>> Help is appreciated
>>
>> Khalil Handal
>>
>>
>
>

[vane0326]

Another way


Try this formula:

=SUM(SUMIF(INDIRECT("Sheet"&{2,3,4,5,6,7,8,9,10,11,12}&"!E:E"),710,INDIRECT("Sheet"&{2,3,4,5,6,7,8,9,10,11,12}&"!C:C")))+SUM(SUMIF(INDIRECT("Sheet"&{2,3,4,5,6,7,8,9,10,11,12}&"!E:E"),710,INDIRECT("Sheet"&{2,3,4,5,6,7,8,9,10,11,12}&"!D:D")))


just hit enter

or if you have your worksheets names in a range say H1:H12 then try this:

=SUM(SUMIF(INDIRECT(H1:H12&"!E:E"),103,INDIRECT(H1:H12&"!C:C")))+SUM(SUMIF(INDIRECT(H1:H12&"!E:E"),103,INDIRECT(H1:H12&"!D:D")))

Must be confirmed with

CTRL+SHIFT+ENTER