Looking for function to pick out names and at values from the same row

Say I have 3 Rows:

Income | Expanses | Memo
|       10.00 | Food
|         5.00 | Gas
|        15.00| Food

Now I'm looking for a function that searches for a string such as
"Food" and then add all Values of the Expanses Tab of this certain tab,
so that all values of  Food will be added automatically so that the
result of this would be 25.00...

I would really appreciate any help


-- 
johnsvolition
------------------------------------------------------------------------
johnsvolition's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27263
View this thread: http://www.excelforum.com/showthread.php?threadid=467730

0
9/15/2005 1:50:32 AM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
451 Views

Similar Articles

[PageSpeed] 37

Assuming the table is in cols A to C, data from row2 down

> Income | Expanses | Memo
> |       10.00 | Food
> |         5.00 | Gas
> |        15.00| Food

Assume in say, E1 down is listed the text:  Food, Gas, etc

Put in F1:
=SUMPRODUCT(--(ISNUMBER(SEARCH(E1,$C$2:$C$10))),$B$2:$B$10)
Copy F1 down

Col F will return the required results

Adapt the ranges to suit, but note that entire col references
cannot be used in SUMPRODUCT

And if the string search needs to be case sensitive,
replace SEARCH with FIND in the formula
(FIND is case sensitive)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"johnsvolition" <johnsvolition.1vdo6c_1126749938.7368@excelforum-nospam.com>
wrote in message
news:johnsvolition.1vdo6c_1126749938.7368@excelforum-nospam.com...
>
> Say I have 3 Rows:
>
> Income | Expanses | Memo
> |       10.00 | Food
> |         5.00 | Gas
> |        15.00| Food
>
> Now I'm looking for a function that searches for a string such as
> "Food" and then add all Values of the Expanses Tab of this certain tab,
> so that all values of  Food will be added automatically so that the
> result of this would be 25.00...
>
> I would really appreciate any help
>
>
> --
> johnsvolition
> ------------------------------------------------------------------------
> johnsvolition's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=27263
> View this thread: http://www.excelforum.com/showthread.php?threadid=467730
>


0
demechanik (4694)
9/15/2005 2:39:37 AM
Reply:

Similar Artilces:

Fixed Assets for GP needs to have a Short Year function
Great PLains Fixed Asset module has to have a Short Year function for financial and tax depreciation calculations. To take a full year of depreciation in a short year is aganist GAAP and Tax Rules. In addition it could cause Audit problems for clients. This has to be fixed ASAP ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsread...

changing name of sender
I forwarded some emails from another computer to my main computer. Now the main computer has the wrong sender (place forwarded from, not original sender). Is there a way I can change the name of the sender so that when I sort emails or search for emails by sender I get the original sender? thanks for any help Huck No. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Huck Rorick" <huckrorick@groundwork.org> wrote in message news:uvyvdS8vKHA.4752@TK2MSFTNGP04.phx.gbl... >I forwarded some emails from another computer ...

Can I change names of fields in calendar?
I am using a calendar like a project manager. I would like to change the names of fields eg Location to Action to be done, Contacts to Action officer. I have been able to change the column headings in Custome view, but this doesn't change teh names of the fields on the calendar form. That is teh form that appears when you make an appointment. No, you can't change them. You could use in-cell editing and create the items in table view, where the column display names can be changed. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook &a...

All my icons look the same (except IE8 and the recycle bin)
i cant open any windows...when i click on them the open with window pops up and wants me to pick wat i want to open it with...i can only get on the internet in safe mode with networking. and when i try to open...for example paint it says invalid bitmap, or its format is not currently supported and other windows wont even open. got any ideas on how i can fix this?? On Sun, 30 May 2010 06:54:01 -0700, xxkoreanxx wrote in message <news:999F2CE9-8E21-47DC-8BA4-BAC1F1329F6E@microsoft.com>: > i cant open any windows...when i click on them the open with window pops up > a...

Requesting Help Ranking Sales Values.....
I have a field in a table named "Ext B/O" that lists the extended Backorder Value for parts sold. There may be duplicate values and they should show the same rankings. I would like to rank these totals from highest to lowest dollar value. I have tried the following query coding but I only get a count of the total number of records in the query for each record (49435) and NOT the individual ranking of the value. Rank: (Select Count(*) from [tbl_WorkingData] Where [Ext B/O] >= 0)+1 Can ANYONE please tell me what I am doing wrong and how to correct it? Th...

Trying to collapse a table, what Function should I use?
I have a table of data (numbers) which is 70 rows by 21 columns, which I would like to summarize into a 5 x 3 table. So every 14 rows by 7 columns sums into a single cell on another worksheet. My original table would be like 1 2 3 ... 20 21 1 2 .... 69 70 and my summary table would be 01-07 08-14 15-21 01-14 A B C 15-28 D E F 29-42 G H I 43-56 J K L 57-70 M N O where A = sum of all numbers in the intersection of rows 1-14 and columns 1-7 where O = sum of all numbers...

Copying Formulas to rows
Hi All, I have created a book which has various formulas accross a row which are all worked out from figures in that row. But I cant figure how to make the whole sheet use these forumlas, i.e. when i start typing info on row 2 it uses the same formulas as on row 1. The only way I have worked out so far to do this is to copy and paste each row, but this is time consuming and im sure there must be a quicker way? Thanks in advance for your help. -- mdj101 ------------------------------------------------------------------------ mdj101's Profile: http://www.excelforum.com/member.php?a...

Changing column names
Can the columns be named ( changing the A B C etcto the months of the year? Cathy No. The column headings are either A,B,C or 1,2,3. You can hide them though, and use Row 1 as your column header row. Then you use whatever you want for the column headers. HTH Otto "Cathy S" <Cathy S@discussions.microsoft.com> wrote in message news:C6DB1DB3-7ED9-4AAD-8395-E9754DCEBA2C@microsoft.com... > Can the columns be named ( changing the A B C etcto the months of the > year? ...

How to Modify an Account Name
We are having an issue when wemodify the account name it changes the owner of the cases associated to the account to the person that modified the account name. Is there a way to change the account name with out having it modify all previous cases or activies ? Thanks Robert Is that happening in the web client or through code? -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.com/crm2/?area=customLookup The JavaScript Snippets Directory: http://www.stunnware.com/crm2/top...

looping through columns and rows
Hello, Can someone show me the code which would allow me to search through a number of rows and columns, to identify a criteria. Each time the criteria is found, 1 is added to a variable. When the variable reaches a set number, the search moves to a different column. This would allow me to allocate people to jobs according to their first, second, third choice etc, without resourcing too many people to a job. can you help? I have very little experience, but am usually able to see what is happening in the code and expend on it to take care of more elements. kind regards, Matt Check one...

Subform comparison of 2 tables excluding non zero value
I am creating a subform (at least that is what I am assuming would be best choice)(Access 2003/NT) in which the user will type in a Melt# (contained in the tblChemicalResults). I need to create a side by side comparison of the chemical requirements from the tblChemicalRequirements to the actual results contained in the tblChemicalResults, but I need it to exclude any non zero values from both tables. Each table contains all of the possible 14 different metal elements that could possibly go into making a particular part. Obviously, not all 14 go into every part, so how do I display only ...

Looking for feedback
Hey everyone just started my own business doing consulting / training for Microsoft Outlook please check out my site www.outlookchallenged.com and provide any suggestions / feedback please. Any advice is good advice at this point. Thanks -- www.outlookchallenged.com Br Sense <outlookhelp@ftard.com> wrote: > Hey everyone just started my own business doing consulting / training > for Microsoft Outlook please check out my site > www.outlookchallenged.com and provide any suggestions / feedback > please. Any advice is good advice at this point. The best advice I have to offer ...

Add quartiles and mode functions to pivots tables in Excel!
If anyone knows of a custom function to calculate quartiles in the pivot table feature in Excel, you'd improve my life! What about the mode function? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=f...

Split Function Wrong Number of Arguments
Hello, I am using the split function to create some arrays in two different mdb's on the same PC. In one mdb, I am getting a "wrong number of arguments" error when I try to complile the code. When I retype the split function, it will only accept the initial string, and no delimiter. I checked all the references and they are identical. Both mdb's are pulling the data from a SQLServer backend. Access 2003, SQLServer 2005. Thanks for any help! -- Albert S. Sorry about this post... Some idiot, (can't imagine who), created a custom function named "...

Generic name for User folder?
Word 2007 Vista Enterprise I have a macro that tests for a certain path; the path is something like: C:\Users\JohnSmith\Documents Other people in my organization might find this macro useful. Is there a generic way to designate the person's User folder (i.e., the "JohnSmith" folder in my example) in VBA, so I don't have to rewrite the path code for each person? Thanks. "wal" <ress6oy8@verizon.net> wrote: > Word 2007 > Vista Enterprise > > I have a macro that tests for a certain path; the path is something > like: > ...

Looking for CRM Solution
Does anyone use, or has anyone encountered, a CRM based solution used for residentail, multifamily property management? On Apr 28, 12:55=A0pm, MrB <M...@discussions.microsoft.com> wrote: > Does anyone use, or has anyone encountered, a CRM based solution used for > residentail, multifamily property management? The beauty of Microsoft CRM is that it can be customized for any business in any industry. It's ability to be customized using front- end tools (not within code) is , as far as I know, unparalleled by any other software package. You have the ability to create new entiti...

=COUNTA() with multiple look-up
Hi All, I would like to count the number of items that have a value "AAA" in column A and an amount >0 in column B. As I am not quite sure how to incorporate two look-ups in a counta formula I was wondering if anyone can give me the formula? Many thanks! Rgds, Robert Try something like this: =SUMPRODUCT((A1:A100="AAA")*(B1:B100>0)) or....if there may be some text cells in B1:B100 interspersed with the numbers =SUMPRODUCT((A1:A100="AAA")*ISNUMBER(B1:B100)*(B1:B100>0)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP &...

Integration Manager--log file name?
Is there any way to know the name of the log file (we're writing our logs to text file) from within one of the "After" integration events? If we're successful, we need to send the user an email telling them that the event is successful. We're currently cycling through the whole folder to find the latest one, but if there were a variable in the Integration Manager VBScripts that I could use, that would be much better. Thanks. PSC: If I recall correctly, IM has a Document Error and Integration Error event in the integration properties. Rather than looking at the...

can I edit a picture to look faded?
I want to edit my picture to be faded so I can use it as background with text over it. "deborahm" <deborahm@discussions.microsoft.com> wrote in message news:4D93DD63-8553-447D-A0AC-44EE0E1BD9B3@microsoft.com... > I want to edit my picture to be faded so I can use it as background with > text > over it. In Publisher you can do it one of two ways. For both ways start by inserting the picture. Method 1, right click on picture and select Format Picture, click on picture tab, then click on the dropdown for Color under Image Control and select wash...

data look up and return values across a row
I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need to cross reference with another larger spreadsheet, which we'll call 'spreadsheet B'. I'd like to remove everything from B that isn't on A. Is there an easy way of doing that? Chip Pearson has lots of info about working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Betsey wrote: > > I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need > to cross reference with another larger spreadsheet, which we'll call > &#...

Name change in mailbox
Would just like to know if someone wants to change their name (because of legal reasons) what is the process and will they loose their old email box?.I have tried to change the details in AD to reflect their new name, changed the exchange email SMTP setting to include their new name, but didnt work. Im sure I don't have to recreate a new AD account and mailbox. Please could someone assist.. Thanks in advance. Nas "Nas" <anonymous@discussions.microsoft.com> wrote: >Would just like to know if someone wants to change their >name (because of legal reasons) wh...

Lookup second/third values
I have a list of items in column a. Every time a value occurs I need to return the value in column B to my sublist in another sheet. Here is what I'm talking about - my list would look like this A B 1 High 2 low 3 Right 1 Left 5 New 1 Old 1 Young 2 Ancient 3 Used Lets say I wanted to display all items from column B where column A = 1. If I use a vlookup, of course it just returns the first value (High) but I need my list on the other sheet to look like this: High Left Old Young I hope this makes sens...

Using Office 2k3 functionality via Information Bridge Framework
Hi all, Firstly, I have overcome many obstacles by reading these newsgroups. Keep up the good work! My CRM installation is ready and I was confronted with the lack of front-end application coupling. Functionality like typing letters should be coupled to Word, as well as making a quote. In my opinion that is. Now I found the IBF (Information Bridge Framework) which should make my life easier. Although during install, I get the following error: "Microsoft CRM is not installed on this computer" Which is absurd, because it is installed and working properly! I followed the diffe...

How can I rewrite IF IsError Function to Work in VBA
I am trying to write this code in VBA. Does anyone know the best code to make this work? If (IsError(mSumIfs / Kountifs),"NA", mSumIfs / Kountifs) If (worksheetfunction.IsError(mSumIfs / Kountifs),"NA", mSumIfs / Kountifs -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=157710 [url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url] "DogLov...

Looking for basic multiplaction formula
HI ALL, I'm trying to multiply cells d5-d9 by cells a5-a9 and put the answer in cells d14 - d18 ( using caps ) I have tried =(D5*A5) in d14 and then pulling it down to d18 PLEASE HELP ME IVE BEEN AT THIS FOR AN HOUR AND A HALF!! Sounds to me like you had it right... what's not working? In cell D14 enter "=D5*A5" Copy that and paste it in cells D15 to D18. Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Concarp wrote: > HI ALL, > I'm trying to multiply cells d5-d9 by cells a5-a9 and > put the answer in cells d14 - d18 ( using caps ) > ...