SUMIF For Duplicate Data Points?

Oct 25, 2013

I was creating a sales report: See below

Sales for Shook, Emily(131520) Total Sales $160.18
Sales for Clayton, Casandra(131008) Total Sales NO SALES FOR THE WEEK
Sales for Jofery, Rebecca(126310) Total SalesNO SALES FOR THE WEEK
Sales for Brea, Olga(124257) Total Sales$140.21
Sales for Mastro, Rachel(131521) Total SalesNO SALES FOR THE WEEK
Sales for Rodriguez, Blanca(111550) Total Sales $154.33
Sales for Katz, Mary Isabel(126840) Total Sales$269.87
Sales for Kitson, Mackenzie(130466) Total SalesNO SALES FOR THE WEEK
Sales for Blagniceanu, Adriana(126518) Total Sales$161.25
Sales for Best, Jessica(128350) Total SalesNO SALES FOR THE WEEK
Sales for Sanchez, Vanessa(126437) Total SalesNO SALES FOR THE WEEK

The Sales For (Last Name, First Name) was a concatenate I created to give everyone a unique identifier. Than I used a vlookup based on the the sales report to get there total sales

=IFERROR(VLOOKUP(D7,sales2,5,FALSE),"NO SALES FOR THE WEEK")

My problem is say for example: Sales for Sanchez, Vanessa shows up twice on the report stating she has total sales for $40 and $60 how can I get excel to calculate that within my VLOOKUP Function. If there a formula I can use to combine both values. I was think SUMIF is the most likely answer but I'm having problems.

View 2 Replies


ADVERTISEMENT

Duplicate Entries For Single Data Points Sets

Mar 6, 2007

I am using old macro code in excel written several years ago. I am trying to educate myself to update and improve the macros I am using but not making much progress. This macro is used to record data from a DDE link to a Data Acquisition System. The D-AQ system sends the data points to r5c3:r5c9 typically. The macro then records the data points in those cells to the next row in sequence on a specific time interval. However, the macro is recording duplicate entries for each set of data points. Sometimes just two, but has been as many as four duplicates. I have a remove duplicates macro to run, after the fact, but preventing them from occurring in the first place is the goal. I included the application.wait method at 10 second intervals after the application .OnTime method, but it isn't preventing the duplicates.

Dim delta_time As Date
Dim Count As Integer
Dim n_rows As Integer
Dim start_row As Integer
Dim col_mov As Integer

Sub initialize_button_set()
start_row = 7
Count = 0
n_rows = Worksheets("sheet1").Cells(1, 2).Value
delta_time = Worksheets("sheet1").Cells(2, 2).Value
Application.OnTime Now + delta_time, "get_data"
Rem Worksheets("sheet1").Buttons.Add 275, 0, 72, 36
Worksheets("sheet1").Buttons.Caption = "STOP"
Worksheets("sheet1").Buttons.OnAction = "Halt"
End Sub...........................

View 2 Replies View Related

Sumif Without Duplicate Records

May 8, 2008

In Column A, is gender: male/female
In Column B, is name: Bob, Jon, Mary, etc
In Column C, is salary: $40, $30, $20, etc.

In column B, there will be instances where a name might appear more than once. I want to be able to do a sumif formula of Column A and Column C, but not include any records where a name in column B is listed more than once. Is there a way to do this?

1 Male Bob $50
2 Female Jane $30
3 Female Kate $20
4 Male Mike $30
5 Male Bob $50

So if I wanted to sum all male salaries above, it would be $80, not $130.

View 9 Replies View Related

SUMIF: Find Out Duplicate Records In My Spreadsheet

Apr 4, 2009

Im trying to find out if there are any duplicate records in my spreadsheet. See look at the attached. Cell 6745 down is highlighted in green. I want to check this against all the cells above. need to write a formula so that it marks any products in the green section with duplicate (in column I) if they are in fact repeated.

View 2 Replies View Related

Points Property Select Multiple Points

May 2, 2007

Is there a way of amending the following line of code so that it selects multiple data points in a data series in a chart (e.g points 14,15,16)?

ActiveChart.SeriesCollection(2).Points(14).Select

View 2 Replies View Related

Color Chart Data Points By Cell Values Of Non-Source Data

Apr 14, 2008

I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?

View 3 Replies View Related

Resample Data: Total Number Of Points Reduced By Averaging The Data Not By Simply Deleting One Of Every Four Rows

May 16, 2006

I have several files of data that are from a data logger. The data is broken up by day. Each day has roughly 43000 rows of data, at its sample rate. This has made charting the data a nuisance. Is there a way to resample the data so it fits in the 32000 points excel can chart? In the future I will set a sample rate that will keep the number of points below the 32K per series. I would like to be able to have the total number of points reduced by averaging the data not by simply deleting one of every four rows.

View 5 Replies View Related

Plotting Data With 35 Points?

Jan 25, 2014

I have a data for sedimentation along a pipe. I measured sediment concentrations at different lengths of the pipe, after different intervals of time. So I have 35 concentration values, take at 5 different points along the pipe, at 7 different times. Is it possible to plot a chart with these 35 points, with depth on y-axis, time on x-axis? These 35 points are percent removals (0-100). .

View 4 Replies View Related

Trend Data Points

Dec 14, 2008

What I have is a column (column A) that only has two data entries at any one time.
I need column B to fill in the trend of those two data points.

My problem is I don't know how to deal with the cells that say #N/A. Which is every cell other than the two cells with data.

If I replace the #N/As with say 1, then it messes up the trend data points.

It seems like I need to be using the trend command, but any solution would be welcome.

View 14 Replies View Related

How To Exclude Certain Data Points

Jan 22, 2007

I'm running a query of 30 and 60 days worth of data but I don't want certain dates to be included in the queries. How do I exclude certain dates and at the same time pull in day 31 to include this in my 30 day high or average etc.?

For example:

1/19/2007 4.7
1/18/2007 4.5
1/17/2007 3.8
1/16/2007 4.9
1/15/2007 3.75
1/12/2007 3.75

I want to exclude 1/15 because it is a holiday and it just repeats the data from 1/12. So I want it to pull in the data for 1/12 if I were doing a 5 day query. As I said before I need to do a MAX, MIN, AVERAGE and STDEV of 30 and 60 days but my formulas are skewed because of the holidays.

View 9 Replies View Related

Directional Data Points

Oct 11, 2007

I have a line chart that has 4 points. Is it possible to make the data points point to the next point? If so how can I do that?

View 9 Replies View Related

Hiding Data Points

Oct 12, 2009

I have created a series of charts in excel and the data sources for them are located on a hidden sheet. I need to keep all the charts active as I have set up macros that enable a viewer to automatically change the chart with a click of a button. However the data behind these charts is confidential and I cannot let the viewer have access to the individual data points.

My issue is that when you 'hover' your mouse over the lines on the charts, the various data points can be seen

Is there a way to make these data points hidden when you hover your mouse over the chart but at the same time keep the source data active behind the chart?

View 9 Replies View Related

Graph Min And Max Data Points

Sep 25, 2007

I need to map the names of the companies on the x axis (ANF, AEO, ARO, PSUN) and 5% to 30% on the y axis. Plotted for each company should be the min and max. So two points for each company. How can I plot this data. Underlying data as follows:

MINMAX
ANF18.6%19.8%
AEO7.8%21.0%
ARO6.2%9.0%
PSUN6.1%14.2%

View 3 Replies View Related

Colour Chart Data Points

May 11, 2007

How do i conditionally colour chart data points. This is an example of my data from which the chart is created from.

Salesman A 10
Salesman B 10
US Total 20
Salesman C 20
Salesman D 10
Philipines Total 30

When the chart is created, i would like the country total data points to be in orange while the rest of the data points should remain in white. I tried this code and it returned me an error.

ActiveChart.SeriesCollection("Average of Margin").Points("ID Total").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

I also tried the online F1 help it told me to use Points(Index). However i do not want to refer to the ponint with an index as the number of salesman or country could change. Therefore changing the index points.

View 4 Replies View Related

Charting 2 Data Series With Different Numbers Of Points

Jun 12, 2014

Have Series 1 (to be a line chart) with 20 data points X values are 0 to 68. Have Series 2 (Bar chart) with 68 data points, again X values 0 to 68

Cant get series 1 to display beyond X value of 20

View 3 Replies View Related

Stretching Chart To Show More Data Points

Nov 26, 2013

Attached is an excel file of my issue. I want to stretch the graph so it shows at least 3-4 months. at the moment is only shows 1 month.

Timeline format - Copy.xlsx‎

View 1 Replies View Related

Graphs With Conditioning Formatting On Data Points?

Dec 17, 2013

Is there any way I can use conditioning formatting on datapoints in a graph, so that if certain points meet the criteria they will appear with another symbol?

Suppose I would like to change symbol on the datapoints exceeding the value 200 in this graph: Graph.jpg

Can I do that?

View 3 Replies View Related

Linear Regression Over The First 5 Points In The Set Of Data On The Same Graph

Mar 2, 2009

I have a set of data. I know how to do linear regression over the whole set of data. How do I have another linear regression over the first 5 points in the set of data on the same graph ?? I am using Excel 2007

View 14 Replies View Related

Sampling Select Data Points At Certain Percentages

Feb 10, 2013

I have a set of data that represents points in a cycle. I would like a way to take a subsample of the data at discrete percentages (i.e., 0%, 5%, 10%......100%).

View 3 Replies View Related

How To Display Custom Tooltips On Data Points

Jun 6, 2014

I have a line chart (multiple series) in which I am trying to display custom tooltips on the data points. The source for these custom tooltips are the Notes columns in the first worksheet. The code (which I've copied from an internet site) is working to some degree but I'm not sure how to modify it to pick up the notes column as the content for the tooltips. Also, the example I took this from only had one line whereas I will have 5 when the chart is complete.

View 6 Replies View Related

Line Chart - How To Ignore Zero Value Data Points

Jan 13, 2009

I have the following chart that displays average speed for my bike rides (Green line). The problem is that I don't want the line going to points with no value or zero value. So, the first month would just be the point then, when I enter in an avg speed in Feb then the line would connect to Feb. Is this possible?

View 9 Replies View Related

Adding Various Time Data Points In One Cell

Jan 10, 2013

I have a number of time data elements of different recordings that i would like to be able to use one formula in one cell and have it give me to total time. For example:

00:03
00:55
00:47
00:05

All in one cell (i.e., not having each data point in a cell), I would like to have the formula tell me that these four recording times equal 00:01:48 (i.e., 1min 48 sec).

View 1 Replies View Related

Excel - Draw Rectangle From Two Data Points?

Sep 5, 2013

I want to put a length in one cell and a width in another. Then have excel draw a square/ rectangle to this size. It could just outline cells, each cell being one inch or any other way.

View 9 Replies View Related

Limit Chart To Certain Number Of Data Points

Jun 3, 2014

I have a macro that runs every 15 minutes and stores data into a table. This table is attached to a chart and auto updates with the new data every 15minutes. The table will house historical data over time, however I only want the chart to reflect the last 96 entries (15min data points over 24hours). So, once i have enough data my table will be A2:A97. When A98 is populated, i want the data in A2 to be removed from the chart only.

Here is a breakdown:

A2:A97 is currently displayed on the chart.
I enter new data into A98.
The chart will now display A3:A98.
I enter new data into A99.
The chart will now display A4:A99.
I enter new data into A100.
The chart will now display A5:A100.
and so on.

How I can accomplish this? Does this need to be done in VBA?

View 3 Replies View Related

Line Chart: How To Ignore Zero Value Data Points

Jan 13, 2009

I have the following chart that displays average speed for my bike rides (Green line). The problem is that I don't want the line going to points with no value or zero value. So, the first month would just be the point then, when I enter in an avg speed in Feb then the line would connect to Feb. Is this possible?

View 9 Replies View Related

Formating Specific Data Points In Chart

May 20, 2007

I have a time series chart ( dates on x axis , with the data values on y axis ). It is a line chart. So column A has the dates, and column B has the values. Both have length 100. Now, column C can be considered to be the "flag" column. If a cell in Column C has the flag set to true , then the corresponding date/data values in the chart needs to be highlighted or formatted differently

I tried playing around with conditional formatting of x-axis but could not make any progress. Do I need to create a new data series with the C column?

Also given that there are 100 date values, I dont want all x-axis points to be listed on the chart. I guess excel by default shows the date points as the user increases/decreases size. But I dont want the specifically formatted date points to be skipped .

View 5 Replies View Related

Align X Y Data Points On Combination Chart

Sep 11, 2007

Following my bosses recent charting attempts involving multicoloured backgrounds, graduated bars, textured boxes, mis-matched fonts etc, etc, which frankly showed no information whatever, I was asked to simplify them.

I did so, as in the two attachments, but the response is now along the lines of "well, yes, but they aren't very exciting, are they?"

View 4 Replies View Related

Show Values Of Charts Data Points

Apr 23, 2008

I've been working on this chart for a while and can't seem to get my x and y axis as well as my series corrected. I have time and distance but since my distances are small I cant seem to make sense on how best to depict my data. Right now it is shown as data points but would love to show value.

View 2 Replies View Related

Data Labels Points In Scatter Plot

Jun 19, 2008

I am attaching the Excel File along with this mail. In chart there is data points have been mentioned in scatter plot like (38,15 etc) Instead of these points I want the names that I have mentioned in column A2:A9. I used to make lots of charts related to this and it is very diffilcult for me to manually punch all these names and most importantly the thing is that I cant download the software available on http://www.appspro.com due to some issues can you give me the VBA code for this which will automatically paste these names only I have to change is the data range and its very urgent................ASAP

View 7 Replies View Related

Formula To Look At All 9 Data Points And Give The Place Number

Jan 19, 2009

Column P is the percentage of body weight lost...

P
-1.98%
-2.92%
-3.87%
-3.48%
0.00%
-0.10%
0.00%
-3.56%
-3.57%

What I need is a formula to look at all 9 data points and give the place number. Remember that the lowest number is actually in first place.

View 2 Replies View Related







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