lookup? #3

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


0
khhandal7314 (196)
3/19/2006 6:21:40 PM
excel 39879 articles. 2 followers. Follow

4 Replies
326 Views

Similar Articles

[PageSpeed] 48

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
>
> 

0
Peo
3/19/2006 8:02:01 PM
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
>
> 


0
3/19/2006 8:33:35 PM
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
>>
>>
>
> 


0
3/19/2006 9:05:53 PM
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 tr
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+ENTE

--
vane032
-----------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1473
View this thread: http://www.excelforum.com/showthread.php?threadid=52406

0
3/19/2006 9:57:00 PM
Reply:

Similar Artilces:

Internet Orders #3
Is there still a way to download internet orders in RMS. I saw the functionality when I first installed the product, but am unable to find it now. Where do you put the server info at for the POS to download the internet orders? I have a web firm that is making a web store for me, and I have a copy of a XML Yahoo receipt. I jsut cannot figure out where to tell RMS to download the orders from. Thanks for the help, Nick http://www.newestech.com They have an add-in called Web Integrator that will download web sales in the Yahoo Stores DTD format and load them into the RMS Database cor...

3-D Graphics?
I seem to have a problem where I switch certain graphics to 3-D and the next time I start money they are back to 2-D. This especially happens with the default "Spending by Category" that is on the money homepage after you log into your file. I've also had this problem with monthly reports. Is there anyplace you can set a default to use 3-D graphics? ...

Wininet #3
Hi, When using Wininet in synchronous mode, do I need to use a thread for my send request ? I'm not sure if you "need to", but I sure would since it would free your UI thread up to process update messages even while data is being moved. Tom "mosfet" <john.doe@anonymous.org> wrote in message news:462ccdbd$0$983$426a74cc@news.free.fr... > Hi, > > When using Wininet in synchronous mode, do I need to use a thread > for my send request ? I prefer using async operations whereever possible and the goal is .... not to block UI thread. For wininet you...

OL2003/ActiveSync 3.7.1 bug?
OL2002 on WM2003, some e-mails were put in Outbox. After ActiveSync, OL2003 on desktop is crashing, apparently due to error while trying to send the last message in outbox after synchronization with the PDA. This message remains in Outbox folder, I cannot delete it (Access denied / moved or already deleted as per error message) and when I try to send it OL2003 crashes. Any help is welcomed! ...

Invalid page fault #3
OUTLOOK caused an invalid page fault in module FLDPUB.DLL at 017f:3f9994f4 This is for Outlook 2000 http://support.microsoft.com?scid=kb;EN-US;Q266732 "sms" <anonymous@discussions.microsoft.com> wrote in message news:017301c3ce33$f904a130$a101280a@phx.gbl... > OUTLOOK caused an invalid page fault in > module FLDPUB.DLL at 017f:3f9994f4 > ...

lookup value
A1= value to look for in B1:B500 if it finds A1 in B1:B500 I want it to return C1. I can use a VLookup ok, except when doesn't find A1 it returns a #N/A. I've tried a IF statement around the VLookup, but still runs into the #N/A issue. Any suggestions? if(isna(vlookup(a1,b1:b500,2,0)),"",(vlookup(a1,b1:b500,2,0)) HTH Regards, Howard "Peter" <Peter@discussions.microsoft.com> wrote in message news:DE12504E-8F96-4D71-A68B-88E0C5F17885@microsoft.com... > A1= value to look for in B1:B500 > if it finds A1 in B1:B500 I want it to return C1. > I can use ...

Profiles #3
i restored my home pc last nite and i cant remember how i set up profiles i am using xp operating system the directions say to go to control panel and double click the mail icon but i cant seem to find it i dont think this is where i did it before please help Dan <anonymous@discussions.microsoft.com> wrote: > i restored my home pc last nite and i cant remember how i > set up profiles i am using xp operating system the > directions say to go to control panel and double click the > mail icon but i cant seem to find it i dont think > this is whe...

MS query #3
I'm bringing over data into Excel using MS Query from another application. One of the fields is a "Note" field. This field comes over including all the formatting. (i.e. "{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0 \fnil\fcharset0 Arial;}{\f1\fnil MS Sans Serif;}} \viewkind4\uc1\pard\lang1033\fs16 Again I say this is just a test.\f1\par }) The technical people at the other application say: "The notes fields are now Info Power Rich Text fields. You will need to use components that reflect them as Rich Text fields. You are apparently using a component that...

Can Lookup Attributes be Updated using CRM 4.0 Data Import?
I wrote some code that uses the CRM 4.0 Data Import functionality to process files that create and update data for custom entities I have created. It was confusing to figure out because of the sparse documentation but it seemed to be working nicely until I noticed that when ImportModeCode is Update the entity attributes that are Lookups don't seem to be getting updated. For example, I created a custom entity named new_lease that has a new_territoryid lookup attribute that looks up it's data from the territory entity. The code populates the new_territoryid value when importing leases...

hi #3
how come if I try to send a message it say doesn't have a digit id for the person I'm sending to. but why it say that and I already have one it shouldn't matter if they do or don't rite. .And I cant even send to yahoo or something come on now yall sorry ...

Publisher 2007 #3
I am trying to upload my web pages to my web site, but one the first page appears. How do I get all the pages to appears? Unless you changed the default settings, when you Publish to the Web, Publisher generates an index.htm file and a index_files folder, that contain your other pages and the supporting graphics. It sounds like you did not upload the index_files folder. If this doesn't solve your problem, please post your follow up questions in the microsoft.public.publisher.webdesign group. DavidF "Mark" <Mark@discussions.microsoft.com> wrote in message news:2F2...

Sales Transaction Entry #3
Is it possible to modify the Sales Transaction Entry window to include the Item Description on the same line as the Item Number? Well you can do this easily using Modifier. Move the fields around pretty much how you want. The only thing is that the Item Description is pretty long and the first "line" is already full. So you'd likely have to either maybe your Item Description pretty small or make the other fields on the first line pretty small. Or move some of those off the first line. Either way, the tool to do this is Modifier. patrick mbs dev support -- This posting i...

Excel Passwords #3
Quick question. Is there anyway I can get into an old employees spreadsheet that is password protected if I don't know the password (its on a MAC). Cheers, Dave Lookup 'Forget Excel Password in Google, you should find some clues there. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave" <anonymous@discussions.microsoft.com> wrote in message news:f90601c3f217$f9c85050$a001280a@phx.gbl... > Quick question. Is there anyway I can get into an old > employees spreadsheet...

Service Pack 3 Problems
I did a recent reformat on my computer and have been progressing through the necessary updates for windows over the past two days. All have loaded on fine except for service pack 3. At first I disabled the antivirus system I have on the computer, attempted another install which too fail. I then went online and access the information found on the microsoft site, (http://support.microsoft.com/default.aspx/kb/949377?p=1). I then continued to follow the steps listed in the advanced section, Method 1: Reset the registry and the file permissions. This did not work, even after res...

If Any one have crm 3.0 installation and config certification dumps
Hi Folks , If Any one have crm 3.0 installation and config certification dumps , Please post it or send to krishnaATgmail.com Thanks Kris ...

Array Formulas #3
Hi Can someone explain when and why you would use an array formula. Just trying to get my head around Thanks A Hi Alex Check this link from CPearson, http://www.cpearson.com/excel/ArrayFormulas.aspx Do a search in Google for "Array Formula in Excel" you'll find all the information you need. HTH John "Alex Hammerstein" <aph@misnet.co.uk> wrote in message news:C698A2ED.EC2B%aph@misnet.co.uk... > Hi > > Can someone explain when and why you would use an array formula. Just > trying to get my head around > > Thanks > > A > You can...

Date Format #3
I have a spreadsheet that I want the date to be formatted as day/month/year (13-09-03). I go to format cells and choose that format for my column. When I type in the date as 13/09/03 it will not convert the date to the correct format. However if I type in 09/13/03 Excel will convert to the proper format. Does this have anything to do with my regional settings in Windows. Would I have to change the setting to day/month/year in windows. I am using Excel 2000. Thanks in advance for your help. Regards, Dee Number format affects display only, not how XL parses an entry. To change the p...

Microsoft CRM 3.0
One of the big issues for the implementation of CRM 1.2 we have is in dealing with multiple currencies across our business. We are not a large business but we have dealings in GBP(£), Euro and USD($). Does anyone know if CRM 3.0 will allow for the currency of an opportunity to be selected or entered. Thanks You might want to look into a solution from Galeforce: http://www.galeforcesolutions.com/Products/ByIndustry/CommercialBanking.htm it supports multicurrency. Regards, Rob Bakkers, Avanade Netherlands "Nigel Swain" wrote: > One of the big issues for the impleme...

Cell Formula #3
I have a spreadsheet with several columns of numbers. These numbers are totaled in one column. I would like the column next to it to show the amount less than 6 and the column next to that to show the amount greater than 6. How do I format the cell to do that? -- dcchica ------------------------------------------------------------------------ dcchica's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30601 View this thread: http://www.excelforum.com/showthread.php?threadid=502532 In the first column type the formula:- =If(C1<6,C1,0) and in the next col...

security_cookie referenced in function _WinMain #3
I am compiling this: http://www.haxorcitos.com/ficheros/minishell.zip with VC++ Exprees and Microsoft SDK platform for windows. I get this errors: MiniShell.obj : error LNK2019: unresolved external symbol ___security_cookie referenced in function _WinMain@16 MiniShell.obj : error LNK2019: unresolved external symbol @__security_check_cookie@4 referenced in function _WinMain@16 Not too much information about them in google. wich dll am I missing? What am I doing wrong? Did you find a solution for this? __security_check_cookie is in the CRT. "VistaUser04" wrote: > I am com...

Contact Sync between CRM 3.0 and Outlook
If I enter a new contact in CRM 3.0 and link it to a parent customer, the sync'ed contact entry in Outlook does not contain the parent account information in the company field on the "General" page although the parent account information made its way into Outlook as a user defined field, being named Parent Account and containing exactly what I would like to see in the company field on the "General" page. Working as designed? Bug? Configurable? Workaround (OL-Makro to move the contents of the "Parent Account" to the "company" field)? Markus ...

Junk Mail #3
Hi, I have set up a filter so that all my junk mail is delivered to the junk mail folder but I cannot seem to get it to delete from the original destination folder so I end up with 2 copies one in the original folder and one in the junk mail folder please can anyone help. Regards Jaimie change your filter to "Move" the message rather than "Copy" it... "Jaimie" <anonymous@discussions.microsoft.com> wrote in message news:099601c3c330$53ad78f0$a501280a@phx.gbl... > Hi, > I have set up a filter so that all my junk mail is > delivered to the jun...

Outlook 2002 Password #3
Recently we Synced a PDA to Outlook 2002, and now Outlook 2002 will not receive any email. All that happens is that Outlook 2002 continually asks for the email password. The email account is a Yahoo account, and everything worked fine, including syncing with the PDA until recently for a few months until this incident. If you can offer any help it would be appreciated. Tony ...

Mail stuck in local delivery queue #3
I'm seeing that quite few people have this problem and I don't see a solution anywhere. My Exchange was working fine until recently. Now all mail only gets as far as the local delivery queue. I've tried eseutil /d and /g, isinteg on the mailboxes - all fine. I've tried reinstalling exch svr 2003 and sp2. One thing I have noticed is that the IS service doesn't stop (in a timely fashion etc) and I can't move mailboxes to new store (just stays at 0%). The Exch server is listed as the GC and DC. Medium diags turned on for DS but nothing untoward appears in app log. ...

Excel Query #3
I want to use an Hanging Indent for the text within a cell. I can do it by spliting that into two different cells but I feel that would be wrong. For example Financial Data for the year ended 2K Please note that the text data should be in the same cell.If any one can help me. Hi can't be done with Excel. You may insert a manual linebreak with ALT+ENTER and insert some spaces but an automatic ident for the second line is not possible with Excel -- Regards Frank Kabel Frankfurt, Germany Felix wrote: > I want to use an Hanging Indent for the text within a > cell. I can do i...