Excel Formulas - Only Add Up Certain Entries

Aug 28, 2013

I am trying to work out a formula, but im having some difficulty, below is what i am trying to do. I need to be able to calculate how much i have billed a client for each month

Sheet 1 - sales ledger

Date Client Amount
14/2 client 1 £100
15/2 client 2 £50
15/2 client 3 £300
16/2 client 1 £200
17/2 Client 3 £500
1/3 client 3 £100
5/3 client 1 £400
10/3 client 2 £500

and so

sheet 2 - Monthly totals for each client

Client Jan, Feb, Mar, Apr etc
Client 1 total for that month amount

etc....

View 6 Replies


ADVERTISEMENT

Using If( And( Formulas To Remove Redundant Entries

Nov 20, 2012

I have a spreadsheet of over 4000 rows. Column 1 is the part, column 2 is the order #, column 3 is the line # from the purchase order, column 4 is cost. I.E. Part- M12053235, Order #- 580001, Line #- 2, Cost- $563.00

Our software duplicated some of the entries and I need to find a way to remove the redundant entries.

I tried using this formula =if(and(b1=b1:b3909,c1=c1:c3909),"redundant","no")

That formula doesn't work because if it does have matching values in column 2(order #) then it looks to see if column 3(line #) matches anywhere in the spreadsheet. Since the data in this column ranges from 1-30, there is always multiple matches. So, my formula shows a redundant entry even when it is not.

I need to find a way so to check if column 3 has matching values in column 3, but only for the range of cells that corresponds to the purchase order in column 2.

View 4 Replies View Related

Excel 2007 :: Formulas In Cells Not Being Recognized As Formulas?

Jan 10, 2013

I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.

View 3 Replies View Related

Getting Excel To Count Text Entries

Jul 18, 2014

I want to learn how to program excel to do this :

1) Type in a text data in a predesignated entry box

2) Excel automatically inserts that text into a table

3) If that text is already in the table, then adds a counter next to it (or increases the counter by one)

4) Excels then clears the text data in the entry box

Basically, I want to tabulate the number of times a specific entry comes up, and it would be much faster if I can just type it in, press enter, and let excel do the rest. It would be ideal if I can also add a date to that entry, so the data table comes up to something like a date vs the entries table, and the intersection would be the number of times each entry comes up on that day.

View 6 Replies View Related

Excel 2010 :: Total Entries Between Two Dates In Table

Mar 17, 2014

I'm an Excel newbie using 2010, but have been given a rather daunting task. I have a lot of data organised in a table, something such as:

Column A: Name, Column B: City, Column C: Date Entered

I can obviously filter this table to show the values I need, but I need it to automatically tell me the total amount of people entered per week, per city on a separate worksheet.

I understand I could use a pivot table for some of this, but I want to further automatically manipulate the data once I have the totals (for example, adding a financial value for each, etc) which I'm not certain how to do with a pivot.

So, ideally what I would like is a formula that says total entries in Table 1 that are marked Sheffield in Column B and have a value including or between 01/03/2014 and 07/03/2014 in Column C.

View 3 Replies View Related

Excel 2007 :: Combining Data With Multiple Row Entries

Mar 7, 2012

I'm using Xl 07 and need to combine two sheets of contact info and have them sorted alphabetically.

Each contact entry takes the follwing form(comma's denote new columns)

55555, Name , John, Smith, Tel: , 555-555-5555
ID , Company, XXX , , Email, xxxxxx

Each sheet has a list of 200 or so entries about 400 rows long. The entries on each sheet are sorted in alphabetical order. the names also alternate alphabetically from sheet to sheet. So when i look at the entries on my final sheet, the 1st will be from sheet1 the second from sheet2 the third from sheet1 and the fourth from sheet2 etc.

I'm not sure whether I should build a new list by pulling alternating entries from each sheet or just copy one list below the other and then try to sort it somehow.

View 1 Replies View Related

User Login To Excel And Edit Their Department Entries

Aug 5, 2012

I have a workbook that has a list of different depts. I need to design a way to enter a login of sorts to the excel and allow the user of that department to edit their entries only and lock the rest of the entries for the rest of the departments and do not show them in the result list after the user logs in. Once all department manager fills in his/her entries, the administrator is notified via email.

View 1 Replies View Related

Excel 2010 :: Automatically Change Text Entries To Set Number ID / VBA?

Jul 24, 2014

Basically I first want to be able define around 100 variables such as:

Dog = 1
Cat = 2
Mouse = 3

And so on. The reason being my final data needs to be the numbers for it's purpose but because there are so many I can't possibly remember them all to just type out 23 | 44 | 76 | 2 etc. What I would like to then happen is that whenever I type out the words in a cell it's automatically replaced with the number ID.

So typing in

Dog
Mouse
Mouse
Cat
Dog

would automatically result in

1
3
3
2
1

In those cells when I hit enter or at the end in one go. Is this at all possible in Excel 2010?

View 1 Replies View Related

Automatically Copy Formula From A Cell When New Entries Added To Excel Database

Mar 16, 2014

I am trying to get VBA code in Excel to copy formula in specific cells to copy the cell below when an entry is added to a database.

View 3 Replies View Related

Excel 2013 :: Count Unique Entries In A Table That Match A Criteria?

Jul 11, 2013

2013

Current Year

Q1-13
Q2-13
Q3-13
Q4-13

Totals

Awarded
£19,000.00
£4,000.00
£3,250.00
£0.00
£26,250.00

[code].....

I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.

View 5 Replies View Related

Excel 2007 :: How To Get Dropdown Box Results To Show Unique Values And Non-Blank Entries

Sep 27, 2012

I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?

View 4 Replies View Related

Using Excel Formulas In VBA

Apr 30, 2009

I need to fill a range (I58:AG66) with either a VLOOKUP or a IF formula based on a selection made in B3 (a dropdown box with about 20 choices).

I've tried typing the formulas in (IF(OR(B3="A",B3="C"),VLOOKUP,IF), but the formula just becomes too long and takes up too much time to recalculate.

Is there a way to go to VBA and have a macro insert either VLOOKUP or IF in each cell in the range. The VLOOKUP looks at the date in each column so each cell needs it's own formula.

View 12 Replies View Related

Excel Recalculates When There Are No Formulas?

Mar 25, 2014

Dealing with a lot of data and a lot of calculations. What I've been doing to manage file size (as I've always done), is to save new versions and paste values of cells that no longer need to change. What started happening though was that each time I saved and/or recalculated, Excel kept taking longer and longer, despite there being only one or two simple calculations (this has never happened before and it's happened twice in the past week).

First incident:
- I tried closing all of the open books and opened just a blank book
- Excel was still recalculating even though it was empty
- I restarted my computer and everything was fine

Second incident
- I tried restarting my computer
- When I reopened the file the problem still existed
- I opened a blank workbook and copied all the values in there
- I closed the original book, then saved the new one, and everything was fine

View 4 Replies View Related

How To Hide Formulas All At Once In Excel

Dec 26, 2012

how can I hide my excel formulas all at once. I know it can be done by pressing F2 on the cell then F9 and then hit enter, but I have to do for each cell,

I'm looking for to hide my formulas all at once, without going through each cell.

View 2 Replies View Related

Excel Formulas Giving 0.00 Value?

Feb 4, 2013

I have a very large excel spreadsheet with many formulas in it. Some of the formulas are giving a 0.00 value even when there should be a $value. I don't know why this is happening? It is not happening to every formula and it is not consistant within the same row or even the same column. Most of these formuals have been used in this spreadsheet for years by many different people with no problems.

View 4 Replies View Related

Copy Excel Formulas Exactly

Mar 28, 2008

Ever have to create a column of formulas that are very similar? When you completed a copy / paste, were you upset to find that the cell references were changed during the paste? I would prefer that Excel leave my formula alone and simply copy the formula from the cell above or where ever without any changes. I want the ability to force Excel to perform an exact copy of the contents or formula from the cell or a range of celle to another cell or range of cells.

I came across this key usage to get an exact copy (no cell reference changes at all) of the cell directly above, Ctrl + '

Is there another key stroke entry that I can use to copy to another location without Excel messing with the formula or cell references?

View 9 Replies View Related

Popular Excel Formulas

Dec 31, 2008

I will be presenting a class on Microsoft Excel at a company conference to possibly 100+ customers. The whole point of the class is to share some knowledge about Excel formulas (not so much the easy ones like SUM or COUNT). I plan on reviewing the following formulas, ones that I think are *somewhat*complicated (when compared to the novice or even medium level user). I'd also like to point out some great formulas that aren't necessarily complicated, but are very useful if you know about them. Are there other great/powerful yet common ones you think I should cover?

- vlookup
- sumproduct
- counta
- countif

View 9 Replies View Related

Excel VBA Code / Hide Formulas

Jan 6, 2012

I am new to programming in excel. For example user enters the value 1 in A1 and 2 in B2 The answer should be in 3 in C1. How to write this code ? Also is there any way where i can let the user copy the VALUE from the result (C1) into another Sheet in case if am using + Sign to obtain the result in C1 instead of writing a VBA Code.

Basically i am looking for the user to in put data in A and B and the result should come in C like wise i want the user to input the values in few rows and the user should get the result in the respetive rows under column C. The user should copy the values from C into another work sheet/ work book. The user should not have access to the formula but should be able to input values and copy the result. can i do this without vba code if so how ?

View 2 Replies View Related

Excel 2007 :: Turning Formulas On And Off

Mar 14, 2012

Background info: I have developed a Hired Equipment Time Sheet with numerous formulas to auto populate and calculate various cells to minimize user impute requirements.

Can cell formulas be toggled on and off to allow manual impute of data rather than the formula automatically populating information in the cell if the need arises? If so, can this be done via a drop down pick? Also can or will the formula still be protected?

View 2 Replies View Related

Excel 2007 :: Not Copying Formulas

Mar 24, 2012

I am running Excel 2007.

I have set up a table and for ease of explanantion Column C has a formulas in it to add together the values found in Column A and B.

EG C3 foumula = "=sum(A3+B3)"

Now in cols d & e I have manually entered figures and in colum F I want to add up those figures so F3 should read "=sum(D3+E3)"

In the past I have always been able to just copy the cell C3 and paste it into F3 and the formula would automatically copy and offset the various cells to the correct cells for the new posiitoning.

However, for some reason when I copy the cell, it now pastes the value only into the pasted cell and does not copy the formula, if I click on paste special to try to just copy the formula I now get a pop up asking me if I want to paste as Unicode Text or Text.

I have no recollection of changing any settings.

View 3 Replies View Related

Save Excel Sheet With Many Formulas

May 25, 2009

I created a financial model in sheet with a macro. The model works as designed. And the workbook can be saved with smaller steps. But with big steps that contains about 250,000 formulas, it seemed to take forever to have the work book saved, I have to canceled it after about 45 minutes. I tried it on different machines and all have the same problems.

View 9 Replies View Related

Excel 2010 :: How To Combine Two Different Formulas Into One Cell

Jun 24, 2014

I need to know if it is possible to combine these 2 formulas (1. (RIGHT(C23,0)&RIGHT(C23,4)) and 2. LEFT(E23,2)&":"&RIGHT(E23,2)
into a single cell. I have a date time group that is in text format. I need to take the last 4 digits (the time) and pull it out into another. Then take that cell and convert it into an actual time ie 2:30.

example: 01may2014 0831 first formula pulls the 0831 into another cell. then the 2nd formula converts it into a time 8:31.

After this occurs, I can then subtract times from different categories in my spreadsheet.

My, problem, is that I cannot combine the two formulas. They work great by themselves. I am just trying to make formulas more compact and hopefully more efficient.

View 5 Replies View Related

Copy And Paste Formulas To New Excel Sheet?

Jan 13, 2014

I was wondering if it's possible to copy and paste formulas to a brand new excel sheet. I created an excel sheet with some forumlas but when I try to copy and paste it to a new page it only pastes the values and not the formulas.

Is there any way to transfer the formulas as well?

View 2 Replies View Related

Excel Reading Numbers As Scientific Formulas?

Dec 11, 2012

When I download data from server side into excel, specific number such as "83437E207" gets converted into scientific number such as "8.3437E+211".

Is there a way to format excel to always to read numeric data as numbers only.

View 7 Replies View Related

Excel 2010 :: Text Contained In Formulas?

Feb 29, 2012

I am working in Excel 2010.

I am looking over spreadsheets in a new job, and I am coming across formulas I am unfamiliar with?

For example, =(G16*G17/C13-G24)*hrs and =I25*elec.

hrs and elec??

After using the "Trace Precedents" feature, the "hrs" and "elec" are still a mystery to me. Are these variables that the creator of the spreadsheet has defined? They don't appear to be named data sets because they exist in just a few cells.

View 3 Replies View Related

Excel 2007 :: Formulas Not Automatically Calculating?

Jul 3, 2012

Using Microsoft Excel 2007 and all of a sudden, my spreadsheets are not automatically calculating the formulas. It does not matter if I have other workbooks open or not. I still get the problem. It does not happen 100% of the time to make it even more complicated.

- Calulation set to auto in Excel Options.

- No VBA functions being used. I can the worksheet summing 1 + 1 and get the error periodically. It does not have to do with the spreadsheet being too complicated.

- Even if I can hit Ctrl-Alt-F9 to force the formulas to calculate, it won't work.

View 2 Replies View Related

Excel 2007 :: Can't Copy / Paste Formulas

Jul 8, 2012

Suddenly realized that I can't paste formulas. When I copy/paste, it only pastes as values. When I copy and Paste Special, the only option is "Unicode Text". No option for formulas, formatting, or anything else. Excel 2007.

View 4 Replies View Related

Summing Up Excel Results In Lookup Formulas?

Jul 25, 2012

My task is to sum up data in a worksheet which counts on the number of "working days"(Exclude Sat, Sun and Public Holiday) between "the date of receiving the case" and "the date of completion" of the same case.

So here I have already listed out the days of each month to distinguish "Working Days" and "Non-working days" by "1" and "0" respectively for the easy calculation of the no. of working days as I think.

like this(January):
Date
Results (Exclude holidays)
1/1/12

[Code].....

Then, I use the lookup formula to determine the receiving date (beginning) and the submission day (Ending) sucessfully.

Receiving date
completion date
Receiving date
completion date

1/3/2012
1/8/2012
1
0

Here comes the problem, I want to sum up the range of cell from the result of 1/3/2012 to 1/8/2012, i.e. (1+1+1+1+0+0)=4 to get the total no. of processing days of each case. I tried to use INDIRECT like "=SUM(INDIRECT(VLOOKUP(F5, A2:A226, B2:B226)):INDIRECT(VLOOKUP(G5, A2:A226, B2:B226)))" but it returns me #REF!

View 3 Replies View Related

Excel 2007 :: Conditional Format With Formulas?

Feb 27, 2013

Is it possible to conditional format cells with formulas in Excel 2007. It used to be possible in Excel 2003 but I'm struggling to find this in the new version.

View 6 Replies View Related

Using EXCEL Formulas With Data Stored On MS ACCESS?

Jun 5, 2013

I am trying to find out if it possible to directly "access" (no play on words here) data stored on MS ACCESS by using Excel's formulas.

I have a set of tables stored on a Microsoft ACCESS 2010 database, and I want to use Excel to analyze that data, using formulas, such as SUMIF.

Is it possible, once I have established an Jet OLEDB connection from Excel to Access, to then directly exploit the data stored on Access without having to copy the data to Excel, and then use it.

I would like Excel to solely act as a tool to analyze, not to store data.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved