Excel 2003 :: How To Add Multiple Values If 2 Cells Match

Dec 2, 2011

I have a sheet that column G and column H has duplicate values. I would like to be able to put a formula in a cell P4 that will search thru column G and column H and if there are any matching items then add the corrisponding number in column I.

So say cell G8, G25, and G30 have the same and H8 and H30 have the same value, i would like this to add the values of cell I8 and I30.

Windows XP
Excel 2003

View 9 Replies


ADVERTISEMENT

Excel 2003 :: Formula For Counting Values Across A Range Using Multiple Criteria Across Multiple Sheets

Feb 9, 2014

I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.

I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.

Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.

I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.

View 12 Replies View Related

Excel 2003 :: Returning Multiple Values From Index?

Apr 20, 2013

I am using the following formula:

=IFERROR(INDEX(drange,SMALL(IF(AND(qrange="SH",trange>30),ROW()-6),ROW(A1))),"")

to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).

There are faults in my formula, and if I'm honest I don't entirely understand it !

I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.

To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.

Using Excel 2003

View 9 Replies View Related

Excel 2003 :: Average Of Cells Which Are Not In Range Which Ignores Any Zero Values

Dec 4, 2013

I have a worksheet which has various figures for each day of the week however I need to establish the weekly average of these figures.

Due to the way in which the figures are displayed, I am unsure how to use a formula which does not require a range with cells located adjacent to one another.

I have attached a test sheet as an example. The cells in yellow require the formula and I need a weekly average for criteria 1-3. This formula also needs to be compatible in Excel 2003

Test Sheet.xls

View 3 Replies View Related

Match Value Of 1 Cell To Multiple Cells And Give Multiple Values

Oct 7, 2006

Sheet 2 is the problem.

I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.

Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.

I have included a sample.

View 9 Replies View Related

Excel 2003 :: Averaging Cells In Multiple Sheets And Not Including Null Or Zero Value?

Jun 24, 2012

I need to calculate the average spend on a day of the week over the month, so all Monday's or all Tuesday's, etc. One sheet is one week so I need to average b16 on 6 sheets as an example.

I used =AVERAGE('WEEK1:WEEK6'!B18) to calculate average over the six Monday's. The issue is, as in other posts, how do I ignore the cells that have a zero or null value.

I've tried adjusting this which was in 1 post
=AVERAGE(IF($C$2:$CA$2=C62,IF($C$25:$CA$25"",$C$25:$CA$25
with this
=AVERAGE(IF(1+1=2,IF('WEEK1:WEEK6 '!B180,'WEEK1:WEEK6 '!B18))) which returns #REF!

this from another post
=SUM('WEEK1:WEEK7 '!B18)/COUNTIF('WEEK1:WEEK7 '!B18,"0") which returns #VALUE!

and this
=AVERAGE(IF('WEEK1:WEEK7 '!B180,'WEEK1:WEEK7 '!B18)) which returns #NAME?

The cells on each sheet are sum formulas for other cells on the sheet not just numbers on their own.

Using windows 7, excel 2003

View 3 Replies View Related

Excel 2003 :: Index And Match - Table With Header Row Of Scores

Oct 4, 2013

Excel 2003

I have a table with a header row of scores 1-10 in B4:K4.

In column A I have a list of locations A5:A68. People in each location were asked to score an event between 1 and 10. The count of their scores is under B4:K4, eg Location 1, 3 people scored 1 (entered in B5), none scored 2 (C5), 6 scored 3 (D5) etc through to the score for 10 in K5.

What I am after is the average for each location so that in L5 I can say 'for all the respondents for Location 1, the average was:...

This may be a bit more complicated than it appears as presumably there will be a requirement to multiply the number of respondents by their scores and then ... ?

View 9 Replies View Related

Excel 2003 :: Double Lookup (Index / Match Or VLookup) Within Same Column?

Mar 15, 2013

I have two spreadsheets, one with master file with original data and one that needs to pull in the original data. My issue is all the data to match off of is in the same column, and a number of other files link to the master file, plus it is used externally, so I cannot alter it, and I would rather not create a mock/copy file. Is there a formula that can look for 2 different items within the same column? Would prefer not to use VBA, but if that's the only option I'll take it. I am using Excel 2003.

Here is an example of the setup - I would need the formula to reference off the two different items/categories in the column, so lookup off the 'St. Louis' and following that, lookup off the produce items.

View 4 Replies View Related

Excel 2003 :: Calculate Sum Based On Partial Text Match And Latest Date

Jun 19, 2014

I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.

A B C
7/1/2012 0:00825-CL-A-41091-REG4.00
7/1/2012 0:00825-CL-A-41091-REG6.25
7/1/2013 0:00825-CL-A-41456-REG4.25
7/1/2013 0:00825-CL-A-41456-REG6.50
1/1/2014 0:00825-CL-A-41640-REG4.25
1/1/2014 0:00825-CL-A-41640-REG6.50
3/1/2014 0:00825-CL-E-41699-REG3.00
3/1/2014 0:00825-CL-E-41699-REG4.00

View 14 Replies View Related

Match Multiple Values With Multiple Values From Another Table - Return Single Value

May 12, 2014

I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...

Attached worksheet : Test booklet.xlsx‎

View 4 Replies View Related

INDEX / MATCH Multiple Ocurence Match Values

Jul 11, 2008

INDEX/MATCH multiple ocurence match values needed

View 9 Replies View Related

Excel 2003 :: Counting Values Within A Variable?

Mar 21, 2014

I am trying to implement a count on a table that splits up the number of customers associated with a list of managers. Sample data attached.

Count function.xlsx

So, I need some way to display a single instance of each manager name with a count of how many customers are associated with that manager. The number of managers and the number of customers associated with each manager does vary and are displayed in separate columns per the attached.

View 14 Replies View Related

Excel 2003 :: 2 Lists - Lookup Different Values

Nov 6, 2013

I have 2 arrays that look like this:

Mercedes ________________Mercedes
BMW ____________________Fiat
Fiat _____________________BMW
VW _____________________AUDI
AUDI_____________________Jeep
Jeep_____________________Porsche
Porsche __________________Ferrari
_________________________Lamborghini
_________________________VW

As you can see, list 2 had some additions (Ferrari,Lamborghini) and a different overall order. I want to input the 2 additions on list 1 right after the last cell(Porsche). List 's 1 order cannot be changed. I have to do it on Excel 2003.

View 4 Replies View Related

Excel 2003 :: SUM With Multiple Criteria?

Oct 20, 2012

I have a table in A2:F200 and want to sum the entries in column C for those rows containing both an F in column E and an L in column F. I thought this would be easy but I cannot figure it out.

I use Excel 2003 so SUMIFS don't work.

View 2 Replies View Related

Excel 2003 :: Conditional Format Top / Mid / Bottom 33% Of Cells But Ignoring Blank Cells

Mar 25, 2012

I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....

Here are the 2 methods Ive tried so far using excel 2003)
Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))

View 4 Replies View Related

Excel 2003 :: How To Ignore Zero Values When Plotting A Graph

Feb 15, 2005

Using Excel 2003. I have a data range for a graph. The values in the cells are the results of a simple If function - If(m28>0,n28,0). The results are taken from a larger data input exercise. But, the graph line (a simple graph!) plots the FALSE value (0) when I would like there to really be no value & hence no plotted point if the result is FALSE.

View 4 Replies View Related

Excel 2003 :: Average Of Values Across 5 Sheets With Two Criteria

Jun 12, 2014

I have a couple of issues and if its okay will post two threads to make it easier to follow due to my basic skills in Excel.

I have a workbook with 5 sheets. The first sheet is a stat sheet which picks up data from four other spreadsheets. I want to work out the average working days taken to complete a case but this data is across multiple sheets.

So for example Column E in each sheet tells you what type of case it is i.e. investigation. Then column T tells you number of workings day it took to complete the case. To work this out I have used the following formula:

=SUM(SUMIF(Further_Action!E:E,"Investigation",Further_Action!T:T),
SUMIF(Court_Application!E:E,"Investigation",Court_Application!T:T),
SUMIF(No_Further_Action!E:E,"Investigation",No_Further_Action!T:T),
SUMIF(Closed!E:E,"Investigation",Closed!T:T))/SUM(COUNTIF(Further_Action!E:E,"Investigation"),
COUNTIF(Court_Application!E:E,"Investigation"),COUNTIF(No_Further_Action!E:E,"Investigation"),
COUNTIF(Closed!E:E,"Investigation"))

I know its very basic but its the only way I know how to work out an average in Excel 2003. I need to add a second criteria where on every sheet it looks in column G for date the case was allocated. As I'm only looking to report in the current report year I want it to look for cases where the value in column G is =>01/04/14 but =<31/03/14.

View 2 Replies View Related

Excel 2003 :: Adding Values If Column Next To It Is In Certain Range

Sep 9, 2013

I have this code working fine in excel 2007 but I need to get it working in excel 2003.

=0.02*(_xlfn.SUMIFS($D$2:$D$98,$C$2:$C$98,">="&I5,$C$2:$C$98,"<="&I4))

I have attached a test document to show what I'd like to do. It is basically adding values if the column next to it is in a certain range.

counting columns in Excel 2003.xls

View 3 Replies View Related

Excel 2003 :: Finding 3rd / 2nd And Highest Values In A Column?

Mar 7, 2013

I have a spreadsheet and on sheet 1 it currently has 45791 rows filled with data and it increases each day.

Each row looks like this

A RACECOURSE
B DATE
C RACE DETAILS
D HORSES NAME
E RATING OF THE HORSE

On Sheet 2 I have 3 columns. These are the list of horses that are running on a particular day.

A DATE
B RACECOURSE
C NAME OF RACEHORSE

What I want to do is to be able to list in columns D,E,and F on sheet 2 the last three ratings the horses achieved from sheet 1.

how to acquire the latest rating of the horse by using the following formula.

=IF(ISNUMBER(v(LOOKUP(9.9999999999999E+307,1/SEARCH(C2,Sheet1!$D$3:$D$45790),Sheet1!$E$3:$E$45790))),v(),"")

This works a treat but for the life of me I can't fathom out how to get the formula to pick the latest three ratings and place them on sheet 2.

I am using excel 2003

View 9 Replies View Related

Excel 2003 :: COUNTIF With Multiple Criteria

Jan 26, 2013

How many tickets are created and fixed by a user for a certain period. See below. I've tried countif but it doesn't seem to be working for many criteria.

Start Date
11/1/2012

End Date
11/31/2012

Assigned To
Created
Fixed

[Code] .....

View 8 Replies View Related

Excel 2003 :: How To Use Filter On Multiple Data

Nov 24, 2013

I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.

Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?

Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?

View 1 Replies View Related

Excel 2003 :: Ignoring Null Values When Plotting Bar Chart?

Jan 21, 2014

I have some data that I'm plotting on a bar chart and I'm trying to "HIDE" the columns with zero or null values. Basically, if the column is blank, I don't want a "gap" on the chart. I'm not getting this to work.

View 6 Replies View Related

Excel 2003 :: Returning The Date In Column For Matching Values

Apr 4, 2014

I am using Excel 2003. I have attached a data file here. getting the values in Q3, R3 and S3.

Scenario:

Q1 has the number = 1. So I want the cell Q3 to return 2/11/2013 as that is the cell corresponding to the Item1 (value specified in P3) with the value 1(value specified in Q1) in the cell. Basically, I need the date corresponding to cell which has the value of Q1 for the value of P3.

Similarly, R2 must have the value 2/12/2013 and S3 must have the value 2/14/2013 returned.

View 10 Replies View Related

Excel 2003 :: Pivot Chart Displaying Added Values?

May 31, 2012

I'm using Windows XP with MS Excel 2003. I have a pivot table representing a survey. Let's say I've built the survey outside of excel and I've imported the response data into Excel. One of the questions in the survey is "ratings" and the possible valid responses for it is: "Excellent", "Good", or "Poor". In my data set in excel let's say I have 10 responses or rows and all the responses for the question on ratings are either "Excellent" or "Good". (There are no rows with a "Poor" value in the ratings column).

For example, let's say out of the 10 responses, 6 are "Excellent" and 4 are "Good". As such my Pivot chart shows two bars: one for the number of respones with "Excellent" (10) and another bar for the number of responses with "Good" (4). My delima is how to show a third bar showing "Poor" with a zero as the number of responses.

View 2 Replies View Related

Excel 2003 :: Match Two Sets Of Data And Display Specific Data?

May 12, 2012

I have two separate worksheets:

I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)

Referral
A
B
C

[Code]....

way to do the calculation using Excel 2003

View 9 Replies View Related

Excel 2003 :: How To Use Countif For Multiple Range And Criteria

Jan 1, 2014

How to use countif in Excel 2003, for multiple range and criteria ? Can 'Nested ifs' be used? If so, a sample of such ifs

View 3 Replies View Related

Excel 2003 :: Finding Average With Multiple Spreadsheets?

Dec 24, 2012

I am currently using Excel 2003. I have a worksheet with two tabs.

First tab has a list of bank Names. Second list has Bank Names and balances.

I need to find out the the average from a Bank in the first tab, to the same bank on the second tab that reflects the balances.

View 6 Replies View Related

Excel 2003 :: Pull Data From Multiple Sheets?

Mar 20, 2014

So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.

I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.

I don't have Excel 2007, only 2003 I have.

View 1 Replies View Related

Excel 2003 :: Macro To Hide Multiple Sheets?

May 10, 2012

I wrote a macro to select multiple sheets by name and hide them, but I keep getting the following error message:

"Object variable or with block variable not set".

Below is the macro:

Sub HideSheets1A()
Dim ws As Worksheet
Application.DisplayAlerts = False
If ws.Name = "Variance Evaluation" Or "Investment" Or "Costs & Incentives" Or "Revenues Total" Then ws.Visible = False
End Sub

I use Excel 2003

View 5 Replies View Related

Excel 2003 :: Export Multiple Worksheets To HTML

Nov 28, 2012

Link: Create a workbook from every worksheet in your workbook

It works great, but is it possible to modify this to export each worksheet as an html file instead of an Excel workbook?

I'm using Excel 2003.

View 2 Replies View Related







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