I have a large data set with over 200,000 rows and I inserted a table.
Which is faster, and results in a smaller file size - using a Pivot Table or Sumifs formulas to get totals of segments of the data?
Trying to use Jeanie for the first time so please excuse if done incorrectly. I am trying to use sumproduct to return sum of red font, sum of black font, sum of green font. Is there a better way.
Thanks.
Q4 Daily
A
B
C
D
E
F
G
H
I
J
K
L
M
559
FCD Week
FCD
41
42
43
44
45
46
47
48
49
50
51
560
40
25
24
561
41
70
27
16
2
35
1
562
42
169
15
30
6
563
43
100
347
108
53
3
25
25
564
44
25
938
1011
207
210
3
13
565
45
314
196
704
2097
710
252
25
7
32
566
46
38
439
2052
791
359
43
26
567
47
200
100
25
189
131
900
1546
1003
419
25
25
568
48
42
45
86
921
1005
1051
128
25
569
49
5
17
51
94
125
1402
1801
612
71
570
50
6
21
102
955
2144
371
571
51
20
1
1131
2561
572
52
18
9
926
573
53
20
5
Excel tables to the web >> Excel Jeanie HTML 4
HI again all.
Ok my problem is this. I have a pivot tanle that check a load of data a filter out what i need. It filter out month, year, severity and client.
The pivot table shows this:
Month: April
Severity: 1
Year: 2009
Client: ABC
Total Count = 3
I am trying to do the same thing with the following formula:
=SUMPRODUCT(--(Data!$AB$2:$AB$65000=2009),--(Data!$AC$2:$AC$65000="April"),--(Data!$I$2:$I$65000=1),--(Data!$K$2:$K$65000="ABC"))
this give me a total count = 0.
The reason for the us eof the formula is that the pivot table compares two years, and only shows blank cells if at least 1 year has data
eg
Jan 2008 = 0
Jan 2009 = 1
the above is shown, the below is not:
Jan 2009 = 0
Jan 2008 = 0
I need to be able to see all not just the stuff with data..
I have a pivot table in a "password protected workbook" that is having "integrity problems" and is being "discarded" each time the workbook is opened.
My pivot table is based off a "dynamic named range".
I have seen comments on this forum to use sumproduct instead of pivot tables, but I ma struggling with this due to the "dynamic range".
How can I use sumproduct with a dynamic range (changing number of rows, fixed columns database)?
Dear Excel gurus-
I am trying to use sumproduct in a calculated field in a pivot table (sumproduct(Sens,Units)/sum(units)). The pivot table is giving me the same results as when I use (Sens*Units)/Units. Sens has a range of values from 0 to 1, but I'm getting pivot table values of greater than 1 when I group by GL Key.
Can I use sumproduct in this manner in a pivot table?
My source data is set up:
Procedure Carrier Units Sens GLKey[/b]
30200034 CarrierA 5 0.5 302
30200035 CarrierB 6 0.2 302
30200037 CarrierC 9 0.0 302
Thanks!
Peachyk in VA
Is it possible to perform a Sumproduct in a pivot table? I'd like to see the weighted average interest rates grouped by a type of product. I can subtotal the data and manually calculate the sumproduct for each grouping. But I wasn't sure if there's a simpler way. I'm running Excel 2010.
Hi all
I have bunch of data that I'm recording on a weekly basis. Just a data of how our suppliers are constantly slipping with the delivery dates and provide us empty promises. that they wont even meet.
I would like to convert this data into a nice charts. I have done lots of charts before and some of them even with formulas that will sort of maintain themselves. But this time I'm in a pickle and I don't even understand the concept how to achieve something that I would like to do. I cant visualize it.
I think I have 3 options. Either to use sumproduct or getpivotdata or some VBA based solution. With sumproduct I'm now quite familiar. I'm very new to "getpivotdata" but I like what pivot tables themselves can do. I have sort of figured out the basics with "getpivotdata" but there is still lot more to learn. I understand how Pivot tables work but I'm not fond of Pivot charts. I don't like them. With any sort of VBA solutions I wold need most help as there is so much to it.
Data itself is vertically ever growing. It expands about 600 lines per month (so not that much). This shouldn't be too much problem for sumproduct formulas or for pivot tables. I would only compose these charts once a week or twice a week (depending how much problems will the suppliers cause). Though I wouldn't want the formulas to slow my workbook down too much as I'm using it every day.
I would like to use a separate sheet within the workbook to get in some summarizing figures in. Like in one week there might be 2-3 days when some dates are slipping.
I would like the chart to present shis info in a form like: on the 25/06/2011 X-amount of dates were slipping; on X occasions reasons for the slips were provided; total amount of days slipped X. then on 27/06/2011 it was this, that and the other. I would like to have a method where I could choose the start date and the end date and have a chart to cover certain period. This would give a very good overview of the problems we have in more longer term.
I could probably figure out the formulas to use to do get this summarised information on a spreadsheet but visually I have no clue how to set it up and turn this to charts in a way I wouldn't have to manually adjust the the source for the charts etc.
I have attached xls to this post. It has the raw data and a pivot table as well. I have manually copy pasted and extracted some information from that pivot table to do example 3 charts to give you an impression what I would like to achieve. All the extracted data is highlighted in yellow table (they are all just values, I haven't used any formulas to get them). I have highlighted some cells in green where I would like to have start and a end date to choose the range (this is probably the most tricky part).
Any help would be immensely appreciated.
Cheers
Rain
Hi Excel Gurus,
I would need your help on the below formula (J30).
I want to use the sumproduct function but I havent found a better way than using a lot of "IF and AND function" iand sumproduct functions to count occurences.
Sumproduct function works fine on its own but I dont know how to insert in a classical sumproduct function a function to count in the below example the ALL for all Names or/and Country??
In the example, i have only 2 columns but i ll have to do it with at least 5 and dont want to use a pivot table
I am sure you have once again a great tip to share
Thanks in Advance and hope this is clear.
-----B---------C-------------------H------------I --------- J
29 Name ----Country ------------Name------- Country --- Count
30 Julien -----fr-------------------all----------- fr-------- =2 (see below
31 Cedric---- ger------------------------------------------ formula)
32 Julien -----ger
33 Julien ---- fr
34 Cedric ---- ger
35 Cedric ----ger
J30=IF(AND(H30="all",I30="all"),SUMPRODUCT((B30:B35"zz")*(C30:C35"zz")),IF(AND(H30"all",I30="all"),SUMPRODUCT((B30:B35=H30)*(C30:C35"zz")),IF(AND(H30"all",I30"all"),SUMPRODUCT((B30:B35=H30)*(C30:C35=I30)),IF(AND(H30="all",I30"all"),SUMPRODUCT((B30:B35H30)*(C30:C35=I30))))))
HELP!!!! I am racking my brain and Google is not helping.
I track my checkbook online. My goal is to Find out how much I spent on Day Care each year, 01/01/05-12/31/05 and 01/01/06-12/31/06 and 01/01/07-12/31/07.
I would also like to do other categories like Xcel Energy, Minnegasco, Walmart, etc... (I will copy the formula and change the name criteria if needed for them).
I think I should be able to get this in a pivot table (See at bottom) but I am having zero luck. I can get them all listed by individual day in the pivot table, but I can not get a summary for 2005, 2006 or 2007 by Place.
I gave up on the Pivot table and decided to try to do a formula instead. Below is the formula I am trying to do (FYI..cell "I1" has my start date for the formula [01/01/2007] and cell "J1" has the end date [01/01/2008].
=SUM(IF(($A$2:$A$10000>=$I$1),($A$2:$A$10000<=$J$1)*AND($B$2:$B$10000="Day Care"),$C$2:$C$10000))
This formula gives me the sum of all my entries, not just Day Care and not just within the dates I specified.
Below is a sample of the first few lines of my checkbook.
Column A (dt ck is wrote) Column B (Place wrote to) Column C (amt of ck)
DATE PLACE AMOUNT
10/31/05 Pizza Hut $61.41
11/02/05 Falafel King $(4.07)
11/04/05 Steve & Barry's $(19.96)
11/06/05 Falafel King $(4.07)
11/07/05 Wanderer $(28.00)
11/08/05 Athens $(14.89)
11/09/05 Deposit $200.00
11/13/05 Athens $(7.44)
11/13/05 W/D $(20.00)
11/13/05 Rainbow $(60.95)
11/14/05 Target $(7.17)
11/14/05 Park Nicollet $(15.00)
Here is my Pivot Table (As you can see it list out every date. I want it to sum the years).
Sum of AMOUNT PLACE
DATE Day Care Walmart Grand Total
12/24/2005 -57.75 -57.75
1/9/2006 -84.06 -84.06
2/14/2006 -8.31 -8.31
3/11/2006 -56.1 -56.1
5/1/2006 -15.1 -15.1
5/3/2006 -17.88 -17.88
5/11/2006 -25.06 -25.06
5/12/2006 -9.8 -9.8
5/14/2006 -142.05 -142.05
5/16/2006 -7.32 -7.32
5/19/2006 -97.92 -97.92
5/20/2006 -28.98 -28.98
5/31/2006 -54.18 -54.18
6/5/2006 -4.97 -4.97
6/6/2006 -52.25 -52.25
6/13/2006 0 0
6/15/2006 -85.36 -85.36
6/17/2006 -39.05 -39.05
6/21/2006 -58.85 -58.85
6/23/2006 -28.96 -28.96
6/26/2006 -110.83 -110.83
7/3/2006 -21.84 -21.84
7/4/2006 -64.81 -64.81
Grand Total -2889 -5433.98 -8322.98
