
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hello!
I'm not quite sure what I did, but while doing a lot of =SUMPRODUCT and =COUNTIF formulas, I just have managed to hit some kind of button combination to deactivate some default settings; as I mostly think it is tied to the 'Cell Category' to something nonstandard, but which I have already attempted to change manually numerous times.
Anytime I try to use the SUMPRODUCT or COUNTIF formula to compute something very simple, and something I know is true, it always returns a '0' or'#VALUE' error. I've even copied and pasted basic examples from Excel Help, as well as copied and pasted the answer it tells you and it gives me a '0' (when it clearly isn't).
Any help? I know I screwed something stupid up!
Thanks
Similar Excel Video Tutorials
Calculations, Operators, Formulas
 See how to make calculations in Excel using formulas and cell references. Learn about the different Excel Math operators and functions that you can us ...
Formulas w Cell References
 The Excel Basics Series shows a systematic description of what Excel can do from beginning to end. #3 Video topics: 1)Formulas 2)Equal ...
Formula Or Function Confusion
 Learn about the differences and similarities between a formula and a function! See: 1) definitions for formulas and functions; 2) Math formulas; 3) Te ...
Time Your Formulas For Speed
 See how to compare alternative formulas to see which is faster. Learn how to copy VBA code from an online source, paste it into the VBA editor so that ...
Similar Topics
Hi,
I am working on a report where I need to use a countif & Sumproduct together. Firstly Is this possible?!
What I'm wanting to do is for the "COUNTIF" formula to count how many times a name appears in column B, then the "SUMPRODUCT" adds toghether how many items of work appear in column C between a certain date range
I have these 2 formulas,
=(COUNTIF($B$2:$B$2256,D3))
=(SUMPRODUCT(($A$2:$A$2556>=$D$1)*($A$2:$A$2556<=$E$1)*($C$2:$C$2556)))
By themselves they work, however when I add them together it just seems to ignore the COUNTIF and focus on the SUMPRODUCT.
Can anybody help me
I have the following code so far, but I keep getting a runtime error.
Sub Macro7()
'
' Macro7 Macro
'
'
Range("D6").Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT(('1'!$F$6:$F$30=RC[1])*('1'!$G$6:$G$30=RC[2])*('1'!$H$6:$H$30))+SUMPRODUCT(('2'!$F$6:$F$30=RC[1])*('2'!$G$6:$G$30=RC[2])*('2'!$H$6:$H$30))+SUMPRODUCT(('3'!$F$6:$F$30=RC[1])*('3'!$G$6:$G$30=RC[2]) " & _
"*('3'!$H$6:$H$30))+SUMPRODUCT(('4'!$F$6:$F$30=RC[1])*('4'!$G$6:$G$30=RC[2])*('4'!$H$6:$H$30))+SUMPRODUCT(('5'!$F$6:$F$30=RC[1])*('5'!$G$6:$G$30=RC[2])*('5'!$H$6:$H$30))+SUMPRODUCT(('6'!$F$6:$F$30=RC[1])*('6'!$G$6:$G$30=RC[2])*('6'!$H$6:$H$30))+SUMPRODUCT(('7'!$F$6:$F$30=RC[1])*('7'!$G$6:$G$30=RC[2]) " & _
"*('7'!$H$6:$H$30))+SUMPRODUCT(('8'!$F$6:$F$30=RC[1])*('8'!$G$6:$G$30=RC[2])*('8'!$H$6:$H$30))+SUMPRODUCT(('9'!$F$6:$F$30=RC[1])*('9'!$G$6:$G$30=RC[2])*('9'!$H$6:$H$30))+SUMPRODUCT(('10'!$F$6:$F$30=RC[1])*('10'!$G$6:$G$30=RC[2])*('10'!$H$6:$H$30))+SUMPRODUCT(('11'!$F$6:$F$30=RC[1])*('11'!$G$6:$G$30=RC[2])*('11'!$H$6:$H$30))+SUMPRODUCT(('12'!$F$6:$F$30=RC[1])*('12'!$G$6:$G$30=RC[2]) " & _
"*('12'!$H$6:$H$30))+SUMPRODUCT(('13'!$F$6:$F$30=RC[1])*('13'!$G$6:$G$30=RC[2])*('13'!$H$6:$H$30))+SUMPRODUCT(('14'!$F$6:$F$30=RC[1])*('14'!$G$6:$G$30=RC[2])*('14'!$H$6:$H$30))+SUMPRODUCT(('15'!$F$6:$F$30=RC[1])*('15'!$G$6:$G$30=RC[2])*('15'!$H$6:$H$30))+SUMPRODUCT(('16'!$F$6:$F$30=RC[1])*('16'!$G$6:$G$30=RC[2])*('16'!$H$6:$H$30))+SUMPRODUCT(('17'!$F$6:$F$30=RC[1])*('17'!$G$6:$G$30=RC[2])*('17'!$H$6:$H$30))+SUMPRODUCT(('18'!$F$6:$F$30=RC[1])*('18'!$G$6:$G$30=RC[2]) " & _
"*('18'!$H$6:$H$30))+SUMPRODUCT(('19'!$F$6:$F$30=RC[1])*('19'!$G$6:$G$30=RC[2])*('19'!$H$6:$H$30))+SUMPRODUCT(('20'!$F$6:$F$30=RC[1])*('20'!$G$6:$G$30=RC[2])*('20'!$H$6:$H$30))+SUMPRODUCT(('21'!$F$6:$F$30=RC[1])*('21'!$G$6:$G$30=RC[2])*('21'!$H$6:$H$30))+SUMPRODUCT(('22'!$F$6:$F$30=RC[1])*('22'!$G$6:$G$30=RC[2])*('22'!$H$6:$H$30))+SUMPRODUCT(('23'!$F$6:$F$30=RC[1])*('23'!$G$6:$G$30=RC[2])*('23'!$H$6:$H$30))+SUMPRODUCT(('24'!$F$6:$F$30=RC[1])*('24'!$G$6:$G$30=RC[2]) " & _
"*('24'!$H$6:$H$30))+SUMPRODUCT(('25'!$F$6:$F$30=RC[1])*('25'!$G$6:$G$30=RC[2])*('25'!$H$6:$H$30))+SUMPRODUCT(('26'!$F$6:$F$30=RC[1])*('26'!$G$6:$G$30=RC[2])*('26'!$H$6:$H$30))+SUMPRODUCT(('27'!$F$6:$F$30=RC[1])*('27'!$G$6:$G$30=RC[2])*('27'!$H$6:$H$30))+SUMPRODUCT(('28'!$F$6:$F$30=RC[1])*('28'!$G$6:$G$30=RC[2])*('28'!$H$6:$H$30))+SUMPRODUCT(('29'!$F$6:$F$30=RC[1])*('29'!$G$6:$G$30=RC[2])*('29'!$H$6:$H$30))+SUMPRODUCT(('30'!$F$6:$F$30=RC[1])*('30'!$G$6:$G$30=RC[2]) " & _
"*('30'!$H$6:$H$30))+SUMPRODUCT(('31'!$F$6:$F$30=RC[1])*('31'!$G$6:$G$30=RC[2])*('31'!$H$6:$H$30))+SUMPRODUCT(('32'!$F$6:$F$30=RC[1])*('32'!$G$6:$G$30=RC[2])*('32'!$H$6:$H$30))+SUMPRODUCT(('33'!$F$6:$F$30=RC[1])*('33'!$G$6:$G$30=RC[2])*('33'!$H$6:$H$30))+SUMPRODUCT(('34'!$F$6:$F$30=RC[1])*('34'!$G$6:$G$30=RC[2])*('34'!$H$6:$H$30))+SUMPRODUCT(('35'!$F$6:$F$30=RC[1])*('35'!$G$6:$G$30=RC[2])*('35'!$H$6:$H$30))+SUMPRODUCT(('36'!$F$6:$F$30=RC[1])*('36'!$G$6:$G$30=RC[2]) " & _
"*('36'!$H$6:$H$30))+SUMPRODUCT(('37'!$F$6:$F$30=RC[1])*('37'!$G$6:$G$30=RC[2])*('37'!$H$6:$H$30))+SUMPRODUCT(('38'!$F$6:$F$30=RC[1])*('38'!$G$6:$G$30=RC[2])*('38'!$H$6:$H$30))+SUMPRODUCT(('39'!$F$6:$F$30=RC[1])*('39'!$G$6:$G$30=RC[2])*('39'!$H$6:$H$30))+SUMPRODUCT(('40'!$F$6:$F$30=RC[1])*('40'!$G$6:$G$30=RC[2])*('40'!$H$6:$H$30))+SUMPRODUCT(('41'!$F$6:$F$30=RC[1])*('41'!$G$6:$G$30=RC[2])*('41'!$H$6:$H$30))+SUMPRODUCT(('42'!$F$6:$F$30=RC[1])*('42'!$G$6:$G$30=RC[2]) " & _
"*('42'!$H$6:$H$30))+SUMPRODUCT(('43'!$F$6:$F$30=RC[1])*('43'!$G$6:$G$30=RC[2])*('43'!$H$6:$H$30))+SUMPRODUCT(('44'!$F$6:$F$30=RC[1])*('44'!$G$6:$G$30=RC[2])*('44'!$H$6:$H$30))+SUMPRODUCT(('45'!$F$6:$F$30=RC[1])*('45'!$G$6:$G$30=RC[2])*('45'!$H$6:$H$30))+SUMPRODUCT(('46'!$F$6:$F$30=RC[1])*('46'!$G$6:$G$30=RC[2])*('46'!$H$6:$H$30))+SUMPRODUCT(('47'!$F$6:$F$30=RC[1])*('47'!$G$6:$G$30=RC[2])*('47'!$H$6:$H$30))+SUMPRODUCT(('48'!$F$6:$F$30=RC[1])*('48'!$G$6:$G$30=RC[2]) " & _
"*('48'!$H$6:$H$30))+SUMPRODUCT(('49'!$F$6:$F$30=RC[1])*('49'!$G$6:$G$30=RC[2])*('49'!$H$6:$H$30))+SUMPRODUCT(('50'!$F$6:$F$30=RC[1])*('50'!$G$6:$G$30=RC[2])*('50'!$H$6:$H$30))+SUMPRODUCT(('51'!$F$6:$F$30=RC[1])*('51'!$G$6:$G$30=RC[2])*('51'!$H$6:$H$30))+SUMPRODUCT(('52'!$F$6:$F$30=RC[1])*('52'!$G$6:$G$30=RC[2])*('52'!$H$6:$H$30))+SUMPRODUCT(('53'!$F$6:$F$30=RC[1])*('53'!$G$6:$G$30=RC[2])*('53'!$H$6:$H$30)) "
Selection.AutoFill Destination:=Range("D6:D24")
Range("D6:D24").Select
Range("E4").Select
End Sub
The formula was copied straight from excel and its main function is to loop through target columns in several sheets. Depending on the search criteria it will display an accummulated value in a table. Any suggestions are welcome.
I've work hard on this problem and after posting the same problem on below forum got some exceptional answer. I do'nt think so that there can be any more improvements but might be some "Genius" came up with extraordinary solution like I am looking for a solution in a single column G:
http://www.excelforum.com/excelgene...inarray.html
I want to get students positions in a class like 1st, 2nd, 3rd, etc. There can be two 1st position and 2 2nd positions at a time .............. So to get this I did following steps:
1.In G Column I put logic for Fail and Obtained Marks.
G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")
2. Then i made array formula in column H to get the position
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
Some improvement to write fail & position
={IF(COUNTIF(B3:F3,">=60")5,"Fail",SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G3))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
And more work to get total marks & position and Fail at the same time......
={IF(COUNTIF(B2:F2,">=60")5,"Fail",SUM(B2:F2)&""&SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
Now I wounder that is there a way to do all calculations in column H without using column G .... ....
By replacing (if there is any method) G2, G3, G4 etc. with Sum(B2:F2); Sum(B3:F3);Sum(B4:F4) etc. in below formula
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
Hope to have a quick & effective solution from experts.
Positions
A
B
C
D
E
F
G
H
1
Students Name
Eng
Math
Phy
Chem
Bio
If Pass Then Marks Else Obtained Marks
If Pass Then Student Position in Class
2
John
85
90
59
75
60
Fail
Fail
3
Maria
85
69
60
85
75
374
3742
4
JANNY
90
60
60
90
60
360
3603
5
Gaughan
0
0
0
0
0
Fail
Fail
6
Madona
75
75
80
65
80
375
3751
7
Wright
75
75
75
80
55
Fail
Fail
8
Trying to get solution as
"If Pass Then Position Else "Fail" "
I want to skip this column and want the same result in Column G
9
10
Spreadsheet Formulas
Cell
Formula
G2
=IF(COUNTIF (B2:F2,">=60") =5,SUM (B2:F2) ,"Fail")
H2
{=IF(COUNTIF (B2:F2,">=60") 5,"Fail",SUM (B2:F2) &""&SUMPRODUCT ( (IF (ISNUMBER ($G$2:$G$7) ,$G$2:$G$7,0) >N (G2) ) /COUNTIF (G$2:G$7,G$2:G$7&"") ) +1)}
G3
=IF(COUNTIF (B3:F3,">=60") =5,SUM (B3:F3) ,"Fail")
H3
{=IF(COUNTIF (B3:F3,">=60") 5,"Fail",SUM (B3:F3) &""&SUMPRODUCT ( (IF (ISNUMBER ($G$2:$G$7) ,$G$2:$G$7,0) >N (G3) ) /COUNTIF (G$2:G$7,G$2:G$7&"") ) +1)}
G4
=IF(COUNTIF (B4:F4,">=60") =5,SUM (B4:F4) ,"Fail")
H4
{=IF(COUNTIF (B4:F4,">=60") 5,"Fail",SUM (B4:F4) &""&SUMPRODUCT ( (IF (ISNUMBER ($G$2:$G$7) ,$G$2:$G$7,0) >N (G4) ) /COUNTIF (G$2:G$7,G$2:G$7&"") ) +1)}
G5
=IF(COUNTIF (B5:F5,">=60") =5,SUM (B5:F5) ,"Fail")
H5
{=IF(COUNTIF (B5:F5,">=60") 5,"Fail",SUM (B5:F5) &""&SUMPRODUCT ( (IF (ISNUMBER ($G$2:$G$7) ,$G$2:$G$7,0) >N (G5) ) /COUNTIF (G$2:G$7,G$2:G$7&"") ) +1)}
G6
=IF(COUNTIF (B6:F6,">=60") =5,SUM (B6:F6) ,"Fail")
H6
{=IF(COUNTIF (B6:F6,">=60") 5,"Fail",SUM (B6:F6) &""&SUMPRODUCT ( (IF (ISNUMBER ($G$2:$G$7) ,$G$2:$G$7,0) >N (G6) ) /COUNTIF (G$2:G$7,G$2:G$7&"") ) +1)}
G7
=IF(COUNTIF (B7:F7,">=60") =5,SUM (B7:F7) ,"Fail")
H7
{=IF(COUNTIF (B7:F7,">=60") 5,"Fail",SUM (B7:F7) &""&SUMPRODUCT ( (IF (ISNUMBER ($G$2:$G$7) ,$G$2:$G$7,0) >N (G7) ) /COUNTIF (G$2:G$7,G$2:G$7&"") ) +1)}
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Excel tables to the web >> Excel Jeanie HTML 4
Question  I have a socalled 'database' of about 15000 rows of data. From
the socalled 'database' I am performing sumproduct type computations. I do
not believe I can use the countif function since I am looking at either 2 or
3 columns of data to extract the proper value (e.g. I can not use countif x
+/ countif y +/ countif z). My question is this  Does the sumproduct
function take a long time to perform calculations? Here are a few of my
formulas
=SUMPRODUCT(('Jan Database'!$C$2:$C$19992=C15)*('Jan
Database'!$P$2:$P$19992=TRUE))
=SUMPRODUCT(('Jan Database'!$C$2:$C$19992=C15)*('Jan
Database'!$P$2:$P$19992=TRUE)*('Jan Database'!$Q$2:$Q$19992))
=SUMPRODUCT(('Jan Database'!$C$2:$C$19992=C15)*('Jan
Database'!$P$2:$P$19992=TRUE)*('Jan Database'!$K$2:$K$19992))
These are all working properly, just a little slow for the user.
Usually i use countif functions to count the number of Y's for yes in cells based on teh criteria. Well it is very error prone. With hundreds of cells the cell ranges can get off and result in inaccurate data. Finding the problem can be tendious. Also, you can only add so many countif statements before Excel tells you that you have too many parameters.
So i thought i would use the sumproduct. BUt i can't get it to work the way i want.
Here is the formulas i was trying to use.
=SUM( SUMPRODUCT((A5:A578="Patient Name")*(B5:B578="Y") ),( SUMPRODUCT(A5:A578="Patient Name")*(C5:C578="Y") ),( SUMPRODUCT(A5:A578="Patient Name")*(D5:D578="Y") ),( SUMPRODUCT(A5:A578="Patient Name")*(E5:E578="Y") ),( SUMPRODUCT(A5:A578="Patient Name")*(F5:F578="Y") ))
Different variation that didn't work:
= ( SUMPRODUCT((A5:A578="Patient Name")*(B5:B578="Y") )+( SUMPRODUCT(A5:A578="Patient Name")*(C5:C578="Y") )+( SUMPRODUCT(A5:A578="Patient Name")*(D5:D578="Y") )+( SUMPRODUCT(A5:A578="Patient Name")*(E5:E578="Y") )+( SUMPRODUCT(A5:A578="Patient Name")*(F5:F578="Y") ))
Since I can't attach the file, I've provided this webpage with a jpg. http://www.wingfling.org/excel/forms.htm
There are 20 pages for 200 charts. I thought the sumproduct might be easier than countif but doing it the old way may be my only option. I don't know.
I have multiple cells with long formulas that have fixed references $. I copied and pasted the formulas across rows very nicely. However, when I copy and paste the formulas down columns, the refererence cell needs to change also. Since the formulas are long, I need a way to click on the cell and hit a "magic" button(s) to clear only the $ signs out of the formulas so they can be pasted properly. I hope that makes sense. Thanks, Kenny
So i have a table of formulas to analize data. I have written the data so the range excedes the actual amount of data so i can add subtract etc as the data changes. Well i copied and pasted updated data over the new data and now some of the formulas (all the formulas with sumproduct) show the #Value error. It is basically the same info with some numbers updated etc but all of the columns match the orginal data. Is there a way to fix this?
ex of a formula that shows an error: =SUMPRODUCT(($F$2:$F$400"JS")*($G$2:$G$400="si")*($I$2:$I$400))
Also i am not sure if it matters but info i am copying from is an access file and i am pasting it in an excel file to read the info of the table. But again all the info/columns are the same.
Thank you for help!
my table (below) has an error.
In C2:C9 should be the number of activities per category that have been finished before today.
The best would be to even include the option, if Activitiy Status = "closed"
Feuil3
A
B
C
D
E
F
G
1
Category
# tasks
burned down
due
completed
% done
left
2
Administration
2
#N/A
0
0
0%
2
3
Finance
0
#N/A
0
0
0%
0
4
IT
5
#N/A
0
0
0%
5
5
Marketing
0
#N/A
0
0
0%
0
6
Operations
0
#N/A
0
0
0%
0
7
HR
0
#N/A
0
0
0%
0
8
Property & Inventory
10
#N/A
1
1
10%
9
9
Sales
0
#N/A
0
0
0%
0
Worksheet Formulas
Cell
Formula
B2
=COUNTIF( Activity_Project_Group,A2 )
C2
=SUMPRODUCT( ( Activity_Project_Group=A2 )*( Activity_Act.Finish
Countif Problem I have entered the following countif formula :
=COUNTIF(X41:X56,"<92")
I and am trying to enter the following formulas on the same worksheet in adjacent columns:
=COUNTIF(X41:X56,"92")
=COUNTIF(X41:X56,"93")
=COUNTIF(X41:X56,"94")
The last three formulas don't compute properly and I get the "error box" that says the formulas are too similar to formulas in adjacent cells.
Any suggestions?
What I need is to use the reason codes to graph the efficieny of an employee hourly throughout the day. This is what I am starting with. Using a similar chart I got assistance with on here from gardner before but changed a bit. Still not showing values. Can anyone offer some advice on what I may be missing?
1st
A
B
C
D
E
F
K
M
N
O
P
Q
R
S
1
Reson Codes
2
Time
Agent State
Reason Code*
Duration
Hour Start
Hour
10
20
30
40
50
60
3
8:16
Loggedin
0
0:00:00
0:00
0
0
0
0
0
0
4
8:16
Not Ready
32760
0:02:20
1:00
0
0
0
0
0
0
5
8:19
Ready
0
0:00:47
2:00
0
0
0
0
0
0
6
8:19
Not Ready
50
0:05:03
3:00
0
0
0
0
0
0
7
8:24
Ready
0
0:05:10
4:00
0
0
0
0
0
0
8
8:30
Reserved
0
0:00:07
5:00
0
0
0
0
0
0
9
8:30
Talking
0
0:05:53
6:00
0
0
0
0
0
0
10
8:36
Work
0
0:00:08
7:00
0
0
0
0
0
0
11
8:36
Ready
0
0:00:00
8:00
0
0
0
0
0
0
12
8:36
Reserved
0
0:00:01
9:00
0
0
0
0
0
0
13
8:36
Talking
0
0:09:57
10:00
0
0
0
0
0
0
14
8:46
Not Ready
30
0:03:29
11:00
0
0
0
0
0
0
15
8:49
Ready
0
0:00:00
12:00
0
0
0
0
0
0
16
8:49
Reserved
0
0:00:03
13:00
0
0
0
0
0
0
17
8:49
Talking
0
0:05:39
14:00
0
0
0
0
0
0
18
8:55
Work
0
0:00:08
15:00
0
0
0
0
0
0
19
8:55
Ready
0
0:00:00
16:00
0
0
0
0
0
0
20
8:55
Reserved
0
0:00:13
17:00
0
0
0
0
0
0
21
8:55
Talking
0
0:07:46
18:00
0
0
0
0
0
0
22
9:03
Work
0
0:00:05
19:00
0
0
0
0
0
0
23
9:03
Not Ready
30
0:00:34
20:00
0
0
0
0
0
0
24
9:04
Ready
0
0:00:00
21:00
0
0
0
0
0
0
25
9:04
Reserved
0
0:00:02
22:00
0
0
0
0
0
0
26
9:04
Talking
0
0:05:47
23:00
0
0
0
0
0
0
Spreadsheet Formulas
Cell
Formula
N2
="10"
O2
="20"
P2
="30"
Q2
="40"
R2
="50"
S2
="60"
K3
=+INT(A3)
M3
=IF(N3>0,K3,"")
N3
=SUMPRODUCT(($A$3:$A$2499>=$K3),($A$3:$A$2499<$K3+1/24),($D$3:$D$2499=N$2))
O3
=SUMPRODUCT(($A$3:$A$2499>=$K3),($A$3:$A$2499<$K3+1/24),($D$3:$D$2499=O$2))
P3
=SUMPRODUCT(($A$3:$A$2499>=$K3),($A$3:$A$2499<$K3+1/24),($D$3:$D$2499=P$2))
Q3
=SUMPRODUCT(($A$3:$A$2499>=$K3),($A$3:$A$2499<$K3+1/24),($D$3:$D$2499=Q$2))
R3
=SUMPRODUCT(($A$3:$A$2499>=$K3),($A$3:$A$2499<$K3+1/24),($D$3:$D$2499=R$2))
S3
=SUMPRODUCT(($A$3:$A$2499>=$K3),($A$3:$A$2499<$K3+1/24),($D$3:$D$2499=S$2))
K4
=K3+1/24
M4
=IF(N4>0,K4,"")
N4
=SUMPRODUCT(($A$3:$A$2499>=$K4),($A$3:$A$2499<$K4+1/24),($D$3:$D$2499=N$2))
O4
=SUMPRODUCT(($A$3:$A$2499>=$K4),($A$3:$A$2499<$K4+1/24),($D$3:$D$2499=O$2))
P4
=SUMPRODUCT(($A$3:$A$2499>=$K4),($A$3:$A$2499<$K4+1/24),($D$3:$D$2499=P$2))
Q4
=SUMPRODUCT(($A$3:$A$2499>=$K4),($A$3:$A$2499<$K4+1/24),($D$3:$D$2499=Q$2))
R4
=SUMPRODUCT(($A$3:$A$2499>=$K4),($A$3:$A$2499<$K4+1/24),($D$3:$D$2499=R$2))
S4
=SUMPRODUCT(($A$3:$A$2499>=$K4),($A$3:$A$2499<$K4+1/24),($D$3:$D$2499=S$2))
K5
=K4+1/24
M5
=IF(N5>0,K5,"")
N5
=SUMPRODUCT(($A$3:$A$2499>=$K5),($A$3:$A$2499<$K5+1/24),($D$3:$D$2499=N$2))
O5
=SUMPRODUCT(($A$3:$A$2499>=$K5),($A$3:$A$2499<$K5+1/24),($D$3:$D$2499=O$2))
P5
=SUMPRODUCT(($A$3:$A$2499>=$K5),($A$3:$A$2499<$K5+1/24),($D$3:$D$2499=P$2))
Q5
=SUMPRODUCT(($A$3:$A$2499>=$K5),($A$3:$A$2499<$K5+1/24),($D$3:$D$2499=Q$2))
R5
=SUMPRODUCT(($A$3:$A$2499>=$K5),($A$3:$A$2499<$K5+1/24),($D$3:$D$2499=R$2))
S5
=SUMPRODUCT(($A$3:$A$2499>=$K5),($A$3:$A$2499<$K5+1/24),($D$3:$D$2499=S$2))
K6
=K5+1/24
M6
=IF(N6>0,K6,"")
N6
=SUMPRODUCT(($A$3:$A$2499>=$K6),($A$3:$A$2499<$K6+1/24),($D$3:$D$2499=N$2))
O6
=SUMPRODUCT(($A$3:$A$2499>=$K6),($A$3:$A$2499<$K6+1/24),($D$3:$D$2499=O$2))
P6
=SUMPRODUCT(($A$3:$A$2499>=$K6),($A$3:$A$2499<$K6+1/24),($D$3:$D$2499=P$2))
Q6
=SUMPRODUCT(($A$3:$A$2499>=$K6),($A$3:$A$2499<$K6+1/24),($D$3:$D$2499=Q$2))
R6
=SUMPRODUCT(($A$3:$A$2499>=$K6),($A$3:$A$2499<$K6+1/24),($D$3:$D$2499=R$2))
S6
=SUMPRODUCT(($A$3:$A$2499>=$K6),($A$3:$A$2499<$K6+1/24),($D$3:$D$2499=S$2))
K7
=K6+1/24
M7
=IF(N7>0,K7,"")
N7
=SUMPRODUCT(($A$3:$A$2499>=$K7),($A$3:$A$2499<$K7+1/24),($D$3:$D$2499=N$2))
O7
=SUMPRODUCT(($A$3:$A$2499>=$K7),($A$3:$A$2499<$K7+1/24),($D$3:$D$2499=O$2))
P7
=SUMPRODUCT(($A$3:$A$2499>=$K7),($A$3:$A$2499<$K7+1/24),($D$3:$D$2499=P$2))
Q7
=SUMPRODUCT(($A$3:$A$2499>=$K7),($A$3:$A$2499<$K7+1/24),($D$3:$D$2499=Q$2))
R7
=SUMPRODUCT(($A$3:$A$2499>=$K7),($A$3:$A$2499<$K7+1/24),($D$3:$D$2499=R$2))
S7
=SUMPRODUCT(($A$3:$A$2499>=$K7),($A$3:$A$2499<$K7+1/24),($D$3:$D$2499=S$2))
K8
=K7+1/24
M8
=IF(N8>0,K8,"")
N8
=SUMPRODUCT(($A$3:$A$2499>=$K8),($A$3:$A$2499<$K8+1/24),($D$3:$D$2499=N$2))
O8
=SUMPRODUCT(($A$3:$A$2499>=$K8),($A$3:$A$2499<$K8+1/24),($D$3:$D$2499=O$2))
P8
=SUMPRODUCT(($A$3:$A$2499>=$K8),($A$3:$A$2499<$K8+1/24),($D$3:$D$2499=P$2))
Q8
=SUMPRODUCT(($A$3:$A$2499>=$K8),($A$3:$A$2499<$K8+1/24),($D$3:$D$2499=Q$2))
R8
=SUMPRODUCT(($A$3:$A$2499>=$K8),($A$3:$A$2499<$K8+1/24),($D$3:$D$2499=R$2))
S8
=SUMPRODUCT(($A$3:$A$2499>=$K8),($A$3:$A$2499<$K8+1/24),($D$3:$D$2499=S$2))
K9
=K8+1/24
M9
=IF(N9>0,K9,"")
N9
=SUMPRODUCT(($A$3:$A$2499>=$K9),($A$3:$A$2499<$K9+1/24),($D$3:$D$2499=N$2))
O9
=SUMPRODUCT(($A$3:$A$2499>=$K9),($A$3:$A$2499<$K9+1/24),($D$3:$D$2499=O$2))
P9
=SUMPRODUCT(($A$3:$A$2499>=$K9),($A$3:$A$2499<$K9+1/24),($D$3:$D$2499=P$2))
Q9
=SUMPRODUCT(($A$3:$A$2499>=$K9),($A$3:$A$2499<$K9+1/24),($D$3:$D$2499=Q$2))
R9
=SUMPRODUCT(($A$3:$A$2499>=$K9),($A$3:$A$2499<$K9+1/24),($D$3:$D$2499=R$2))
S9
=SUMPRODUCT(($A$3:$A$2499>=$K9),($A$3:$A$2499<$K9+1/24),($D$3:$D$2499=S$2))
K10
=K9+1/24
M10
=IF(N10>0,K10,"")
N10
=SUMPRODUCT(($A$3:$A$2499>=$K10),($A$3:$A$2499<$K10+1/24),($D$3:$D$2499=N$2))
O10
=SUMPRODUCT(($A$3:$A$2499>=$K10),($A$3:$A$2499<$K10+1/24),($D$3:$D$2499=O$2))
P10
=SUMPRODUCT(($A$3:$A$2499>=$K10),($A$3:$A$2499<$K10+1/24),($D$3:$D$2499=P$2))
Q10
=SUMPRODUCT(($A$3:$A$2499>=$K10),($A$3:$A$2499<$K10+1/24),($D$3:$D$2499=Q$2))
R10
=SUMPRODUCT(($A$3:$A$2499>=$K10),($A$3:$A$2499<$K10+1/24),($D$3:$D$2499=R$2))
S10
=SUMPRODUCT(($A$3:$A$2499>=$K10),($A$3:$A$2499<$K10+1/24),($D$3:$D$2499=S$2))
K11
=K10+1/24
M11
=IF(N11>0,K11,"")
N11
=SUMPRODUCT(($A$3:$A$2499>=$K11),($A$3:$A$2499<$K11+1/24),($D$3:$D$2499=N$2))
O11
=SUMPRODUCT(($A$3:$A$2499>=$K11),($A$3:$A$2499<$K11+1/24),($D$3:$D$2499=O$2))
P11
=SUMPRODUCT(($A$3:$A$2499>=$K11),($A$3:$A$2499<$K11+1/24),($D$3:$D$2499=P$2))
Q11
=SUMPRODUCT(($A$3:$A$2499>=$K11),($A$3:$A$2499<$K11+1/24),($D$3:$D$2499=Q$2))
R11
=SUMPRODUCT(($A$3:$A$2499>=$K11),($A$3:$A$2499<$K11+1/24),($D$3:$D$2499=R$2))
S11
=SUMPRODUCT(($A$3:$A$2499>=$K11),($A$3:$A$2499<$K11+1/24),($D$3:$D$2499=S$2))
K12
=K11+1/24
M12
=IF(N12>0,K12,"")
N12
=SUMPRODUCT(($A$3:$A$2499>=$K12),($A$3:$A$2499<$K12+1/24),($D$3:$D$2499=N$2))
O12
=SUMPRODUCT(($A$3:$A$2499>=$K12),($A$3:$A$2499<$K12+1/24),($D$3:$D$2499=O$2))
P12
=SUMPRODUCT(($A$3:$A$2499>=$K12),($A$3:$A$2499<$K12+1/24),($D$3:$D$2499=P$2))
Q12
=SUMPRODUCT(($A$3:$A$2499>=$K12),($A$3:$A$2499<$K12+1/24),($D$3:$D$2499=Q$2))
R12
=SUMPRODUCT(($A$3:$A$2499>=$K12),($A$3:$A$2499<$K12+1/24),($D$3:$D$2499=R$2))
S12
=SUMPRODUCT(($A$3:$A$2499>=$K12),($A$3:$A$2499<$K12+1/24),($D$3:$D$2499=S$2))
K13
=K12+1/24
M13
=IF(N13>0,K13,"")
N13
=SUMPRODUCT(($A$3:$A$2499>=$K13),($A$3:$A$2499<$K13+1/24),($D$3:$D$2499=N$2))
O13
=SUMPRODUCT(($A$3:$A$2499>=$K13),($A$3:$A$2499<$K13+1/24),($D$3:$D$2499=O$2))
P13
=SUMPRODUCT(($A$3:$A$2499>=$K13),($A$3:$A$2499<$K13+1/24),($D$3:$D$2499=P$2))
Q13
=SUMPRODUCT(($A$3:$A$2499>=$K13),($A$3:$A$2499<$K13+1/24),($D$3:$D$2499=Q$2))
R13
=SUMPRODUCT(($A$3:$A$2499>=$K13),($A$3:$A$2499<$K13+1/24),($D$3:$D$2499=R$2))
S13
=SUMPRODUCT(($A$3:$A$2499>=$K13),($A$3:$A$2499<$K13+1/24),($D$3:$D$2499=S$2))
K14
=K13+1/24
M14
=IF(N14>0,K14,"")
N14
=SUMPRODUCT(($A$3:$A$2499>=$K14),($A$3:$A$2499<$K14+1/24),($D$3:$D$2499=N$2))
O14
=SUMPRODUCT(($A$3:$A$2499>=$K14),($A$3:$A$2499<$K14+1/24),($D$3:$D$2499=O$2))
P14
=SUMPRODUCT(($A$3:$A$2499>=$K14),($A$3:$A$2499<$K14+1/24),($D$3:$D$2499=P$2))
Q14
=SUMPRODUCT(($A$3:$A$2499>=$K14),($A$3:$A$2499<$K14+1/24),($D$3:$D$2499=Q$2))
R14
=SUMPRODUCT(($A$3:$A$2499>=$K14),($A$3:$A$2499<$K14+1/24),($D$3:$D$2499=R$2))
S14
=SUMPRODUCT(($A$3:$A$2499>=$K14),($A$3:$A$2499<$K14+1/24),($D$3:$D$2499=S$2))
K15
=K14+1/24
M15
=IF(N15>0,K15,"")
N15
=SUMPRODUCT(($A$3:$A$2499>=$K15),($A$3:$A$2499<$K15+1/24),($D$3:$D$2499=N$2))
O15
=SUMPRODUCT(($A$3:$A$2499>=$K15),($A$3:$A$2499<$K15+1/24),($D$3:$D$2499=O$2))
P15
=SUMPRODUCT(($A$3:$A$2499>=$K15),($A$3:$A$2499<$K15+1/24),($D$3:$D$2499=P$2))
Q15
=SUMPRODUCT(($A$3:$A$2499>=$K15),($A$3:$A$2499<$K15+1/24),($D$3:$D$2499=Q$2))
R15
=SUMPRODUCT(($A$3:$A$2499>=$K15),($A$3:$A$2499<$K15+1/24),($D$3:$D$2499=R$2))
S15
=SUMPRODUCT(($A$3:$A$2499>=$K15),($A$3:$A$2499<$K15+1/24),($D$3:$D$2499=S$2))
K16
=K15+1/24
M16
=IF(N16>0,K16,"")
N16
=SUMPRODUCT(($A$3:$A$2499>=$K16),($A$3:$A$2499<$K16+1/24),($D$3:$D$2499=N$2))
O16
=SUMPRODUCT(($A$3:$A$2499>=$K16),($A$3:$A$2499<$K16+1/24),($D$3:$D$2499=O$2))
P16
=SUMPRODUCT(($A$3:$A$2499>=$K16),($A$3:$A$2499<$K16+1/24),($D$3:$D$2499=P$2))
Q16
=SUMPRODUCT(($A$3:$A$2499>=$K16),($A$3:$A$2499<$K16+1/24),($D$3:$D$2499=Q$2))
R16
=SUMPRODUCT(($A$3:$A$2499>=$K16),($A$3:$A$2499<$K16+1/24),($D$3:$D$2499=R$2))
S16
=SUMPRODUCT(($A$3:$A$2499>=$K16),($A$3:$A$2499<$K16+1/24),($D$3:$D$2499=S$2))
K17
=K16+1/24
M17
=IF(N17>0,K17,"")
N17
=SUMPRODUCT(($A$3:$A$2499>=$K17),($A$3:$A$2499<$K17+1/24),($D$3:$D$2499=N$2))
O17
=SUMPRODUCT(($A$3:$A$2499>=$K17),($A$3:$A$2499<$K17+1/24),($D$3:$D$2499=O$2))
P17
=SUMPRODUCT(($A$3:$A$2499>=$K17),($A$3:$A$2499<$K17+1/24),($D$3:$D$2499=P$2))
Q17
=SUMPRODUCT(($A$3:$A$2499>=$K17),($A$3:$A$2499<$K17+1/24),($D$3:$D$2499=Q$2))
R17
=SUMPRODUCT(($A$3:$A$2499>=$K17),($A$3:$A$2499<$K17+1/24),($D$3:$D$2499=R$2))
S17
=SUMPRODUCT(($A$3:$A$2499>=$K17),($A$3:$A$2499<$K17+1/24),($D$3:$D$2499=S$2))
K18
=K17+1/24
M18
=IF(N18>0,K18,"")
N18
=SUMPRODUCT(($A$3:$A$2499>=$K18),($A$3:$A$2499<$K18+1/24),($D$3:$D$2499=N$2))
O18
=SUMPRODUCT(($A$3:$A$2499>=$K18),($A$3:$A$2499<$K18+1/24),($D$3:$D$2499=O$2))
P18
=SUMPRODUCT(($A$3:$A$2499>=$K18),($A$3:$A$2499<$K18+1/24),($D$3:$D$2499=P$2))
Q18
=SUMPRODUCT(($A$3:$A$2499>=$K18),($A$3:$A$2499<$K18+1/24),($D$3:$D$2499=Q$2))
R18
=SUMPRODUCT(($A$3:$A$2499>=$K18),($A$3:$A$2499<$K18+1/24),($D$3:$D$2499=R$2))
S18
=SUMPRODUCT(($A$3:$A$2499>=$K18),($A$3:$A$2499<$K18+1/24),($D$3:$D$2499=S$2))
K19
=K18+1/24
M19
=IF(N19>0,K19,"")
N19
=SUMPRODUCT(($A$3:$A$2499>=$K19),($A$3:$A$2499<$K19+1/24),($D$3:$D$2499=N$2))
O19
=SUMPRODUCT(($A$3:$A$2499>=$K19),($A$3:$A$2499<$K19+1/24),($D$3:$D$2499=O$2))
P19
=SUMPRODUCT(($A$3:$A$2499>=$K19),($A$3:$A$2499<$K19+1/24),($D$3:$D$2499=P$2))
Q19
=SUMPRODUCT(($A$3:$A$2499>=$K19),($A$3:$A$2499<$K19+1/24),($D$3:$D$2499=Q$2))
R19
=SUMPRODUCT(($A$3:$A$2499>=$K19),($A$3:$A$2499<$K19+1/24),($D$3:$D$2499=R$2))
S19
=SUMPRODUCT(($A$3:$A$2499>=$K19),($A$3:$A$2499<$K19+1/24),($D$3:$D$2499=S$2))
K20
=K19+1/24
M20
=IF(N20>0,K20,"")
N20
=SUMPRODUCT(($A$3:$A$2499>=$K20),($A$3:$A$2499<$K20+1/24),($D$3:$D$2499=N$2))
O20
=SUMPRODUCT(($A$3:$A$2499>=$K20),($A$3:$A$2499<$K20+1/24),($D$3:$D$2499=O$2))
P20
=SUMPRODUCT(($A$3:$A$2499>=$K20),($A$3:$A$2499<$K20+1/24),($D$3:$D$2499=P$2))
Q20
=SUMPRODUCT(($A$3:$A$2499>=$K20),($A$3:$A$2499<$K20+1/24),($D$3:$D$2499=Q$2))
R20
=SUMPRODUCT(($A$3:$A$2499>=$K20),($A$3:$A$2499<$K20+1/24),($D$3:$D$2499=R$2))
S20
=SUMPRODUCT(($A$3:$A$2499>=$K20),($A$3:$A$2499<$K20+1/24),($D$3:$D$2499=S$2))
K21
=K20+1/24
M21
=IF(N21>0,K21,"")
N21
=SUMPRODUCT(($A$3:$A$2499>=$K21),($A$3:$A$2499<$K21+1/24),($D$3:$D$2499=N$2))
O21
=SUMPRODUCT(($A$3:$A$2499>=$K21),($A$3:$A$2499<$K21+1/24),($D$3:$D$2499=O$2))
P21
=SUMPRODUCT(($A$3:$A$2499>=$K21),($A$3:$A$2499<$K21+1/24),($D$3:$D$2499=P$2))
Q21
=SUMPRODUCT(($A$3:$A$2499>=$K21),($A$3:$A$2499<$K21+1/24),($D$3:$D$2499=Q$2))
R21
=SUMPRODUCT(($A$3:$A$2499>=$K21),($A$3:$A$2499<$K21+1/24),($D$3:$D$2499=R$2))
S21
=SUMPRODUCT(($A$3:$A$2499>=$K21),($A$3:$A$2499<$K21+1/24),($D$3:$D$2499=S$2))
K22
=K21+1/24
M22
=IF(N22>0,K22,"")
N22
=SUMPRODUCT(($A$3:$A$2499>=$K22),($A$3:$A$2499<$K22+1/24),($D$3:$D$2499=N$2))
O22
=SUMPRODUCT(($A$3:$A$2499>=$K22),($A$3:$A$2499<$K22+1/24),($D$3:$D$2499=O$2))
P22
=SUMPRODUCT(($A$3:$A$2499>=$K22),($A$3:$A$2499<$K22+1/24),($D$3:$D$2499=P$2))
Q22
=SUMPRODUCT(($A$3:$A$2499>=$K22),($A$3:$A$2499<$K22+1/24),($D$3:$D$2499=Q$2))
R22
=SUMPRODUCT(($A$3:$A$2499>=$K22),($A$3:$A$2499<$K22+1/24),($D$3:$D$2499=R$2))
S22
=SUMPRODUCT(($A$3:$A$2499>=$K22),($A$3:$A$2499<$K22+1/24),($D$3:$D$2499=S$2))
K23
=K22+1/24
M23
=IF(N23>0,K23,"")
N23
=SUMPRODUCT(($A$3:$A$2499>=$K23),($A$3:$A$2499<$K23+1/24),($D$3:$D$2499=N$2))
O23
=SUMPRODUCT(($A$3:$A$2499>=$K23),($A$3:$A$2499<$K23+1/24),($D$3:$D$2499=O$2))
P23
=SUMPRODUCT(($A$3:$A$2499>=$K23),($A$3:$A$2499<$K23+1/24),($D$3:$D$2499=P$2))
Q23
=SUMPRODUCT(($A$3:$A$2499>=$K23),($A$3:$A$2499<$K23+1/24),($D$3:$D$2499=Q$2))
R23
=SUMPRODUCT(($A$3:$A$2499>=$K23),($A$3:$A$2499<$K23+1/24),($D$3:$D$2499=R$2))
S23
=SUMPRODUCT(($A$3:$A$2499>=$K23),($A$3:$A$2499<$K23+1/24),($D$3:$D$2499=S$2))
K24
=K23+1/24
M24
=IF(N24>0,K24,"")
N24
=SUMPRODUCT(($A$3:$A$2499>=$K24),($A$3:$A$2499<$K24+1/24),($D$3:$D$2499=N$2))
O24
=SUMPRODUCT(($A$3:$A$2499>=$K24),($A$3:$A$2499<$K24+1/24),($D$3:$D$2499=O$2))
P24
=SUMPRODUCT(($A$3:$A$2499>=$K24),($A$3:$A$2499<$K24+1/24),($D$3:$D$2499=P$2))
Q24
=SUMPRODUCT(($A$3:$A$2499>=$K24),($A$3:$A$2499<$K24+1/24),($D$3:$D$2499=Q$2))
R24
=SUMPRODUCT(($A$3:$A$2499>=$K24),($A$3:$A$2499<$K24+1/24),($D$3:$D$2499=R$2))
S24
=SUMPRODUCT(($A$3:$A$2499>=$K24),($A$3:$A$2499<$K24+1/24),($D$3:$D$2499=S$2))
K25
=K24+1/24
M25
=IF(N25>0,K25,"")
N25
=SUMPRODUCT(($A$3:$A$2499>=$K25),($A$3:$A$2499<$K25+1/24),($D$3:$D$2499=N$2))
O25
=SUMPRODUCT(($A$3:$A$2499>=$K25),($A$3:$A$2499<$K25+1/24),($D$3:$D$2499=O$2))
P25
=SUMPRODUCT(($A$3:$A$2499>=$K25),($A$3:$A$2499<$K25+1/24),($D$3:$D$2499=P$2))
Q25
=SUMPRODUCT(($A$3:$A$2499>=$K25),($A$3:$A$2499<$K25+1/24),($D$3:$D$2499=Q$2))
R25
=SUMPRODUCT(($A$3:$A$2499>=$K25),($A$3:$A$2499<$K25+1/24),($D$3:$D$2499=R$2))
S25
=SUMPRODUCT(($A$3:$A$2499>=$K25),($A$3:$A$2499<$K25+1/24),($D$3:$D$2499=S$2))
K26
=K25+1/24
M26
=IF(N26>0,K26,"")
N26
=SUMPRODUCT(($A$3:$A$2499>=$K26),($A$3:$A$2499<$K26+1/24),($D$3:$D$2499=N$2))
O26
=SUMPRODUCT(($A$3:$A$2499>=$K26),($A$3:$A$2499<$K26+1/24),($D$3:$D$2499=O$2))
P26
=SUMPRODUCT(($A$3:$A$2499>=$K26),($A$3:$A$2499<$K26+1/24),($D$3:$D$2499=P$2))
Q26
=SUMPRODUCT(($A$3:$A$2499>=$K26),($A$3:$A$2499<$K26+1/24),($D$3:$D$2499=Q$2))
R26
=SUMPRODUCT(($A$3:$A$2499>=$K26),($A$3:$A$2499<$K26+1/24),($D$3:$D$2499=R$2))
S26
=SUMPRODUCT(($A$3:$A$2499>=$K26),($A$3:$A$2499<$K26+1/24),($D$3:$D$2499=S$2))
Excel tables to the web >> Excel Jeanie HTML 4
I'm working in Excel 2007, and am having difficulty pasting formulas. For some reason, when I paste a formula into an adjacent cell, it returns the value of the cell that was copied. OK, so that may sound like I pasted values... but when I click in the formula in the pasted cell, it has updated correctly. And if I hit enter, the correct value is then computed and displayed.
As a result, I can't paste formulas and get the correct result unless I go into the formula in each pasted cell individually and hit enter. Weird. Anybody else experience this, or know how it can be corrected?
Thanks!!
Afternoon,
I have a spreadsheet, basically a more complex version of the attached.
Where in the attached file there are "Total" formulas, in my file there are sumproduct formulas with multiple conditions. In total there are 4 tabs, each having 30 rows, each row containing 31 sumproduct formulas. So in total there are 3,720 sumproduct formulas calculating each time I change something in the spreadsheet which is slowing me down considerably.
One solution I can think of for this problem is to write a macro that if I keep the sumproduct formulas in the first column of each sheet (that's only 120 sumproduct formulas!), the macro will copy the formulas to a particular column and paste values to the previous days column.
For example, today is the 6th so I am calculating results for previous days performance, in the attached spreadsheet that would be column F where the results for the 5th are recorded.
What I am hoping to achieve is to copy formulas from cells B6, 12, 18, 25 and 30 (day one formulas will always be included) into F6, 12, 18, 25 and 30 and paste values in E6, 12, 18, 25 and 30.
The same process will be repeated every day whereby one day has to have formulas copied into it and another will have values pasted. This does not happen every day, as there will be no results on weekends.
Any ideas?
Have no idea where to begin.
In column (DIGIT) are values ranging from 136; in column (SKIPS) are values that range from 0XXX
Using the skip values for 5 digits, create the combination that equals a sum, that will be input.
Example combination 2891013 = 2,7,8,0,16= sum 33; each sum will have more than 1 combination.
SKIPSUMS
T
U
V
W
4
DIGIT
HIT
SKIPS
SKIPSUMS
5
1
332
5
28
6
2
317
6
27
7
3
302
7
34
8
4
313
0
32
9
5
271
7
31
10
6
337
6
25
11
7
326
3
36
12
8
285
7
26
13
9
301
8
35
14
10
310
0
23
15
11
305
17
24
16
12
315
2
30
17
13
298
16
33
18
14
307
1
29
19
15
297
3
37
20
16
306
6
20
Excel 2007
Worksheet Formulas
Cell
Formula
U5
=COUNTIF( $B$14:$F$2216,$T5 )
U6
=COUNTIF( $B$14:$F$2216,$T6 )
U7
=COUNTIF( $B$14:$F$2216,$T7 )
U8
=COUNTIF( $B$14:$F$2216,$T8 )
U9
=COUNTIF( $B$14:$F$2216,$T9 )
U10
=COUNTIF( $B$14:$F$2216,$T10 )
U11
=COUNTIF( $B$14:$F$2216,$T11 )
U12
=COUNTIF( $B$14:$F$2216,$T12 )
U13
=COUNTIF( $B$14:$F$2216,$T13 )
U14
=COUNTIF( $B$14:$F$2216,$T14 )
U15
=COUNTIF( $B$14:$F$2216,$T15 )
U16
=COUNTIF( $B$14:$F$2216,$T16 )
U17
=COUNTIF( $B$14:$F$2216,$T17 )
U18
=COUNTIF( $B$14:$F$2216,$T18 )
U19
=COUNTIF( $B$14:$F$2216,$T19 )
U20
=COUNTIF( $B$14:$F$2216,$T20 )
Array Formulas
Cell
Formula
V5
=IF( COLUMNS( V5:V5 )<=COUNTIF( $B$12:$F$2216,$T5 ),SMALL( IF( $B$12:$F$2216=$T5,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V5:V5 ) )1,"" )
V6
=IF( COLUMNS( V6:V6 )<=COUNTIF( $B$12:$F$2216,$T6 ),SMALL( IF( $B$12:$F$2216=$T6,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V6:V6 ) )1,"" )
V7
=IF( COLUMNS( V7:V7 )<=COUNTIF( $B$12:$F$2216,$T7 ),SMALL( IF( $B$12:$F$2216=$T7,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V7:V7 ) )1,"" )
V8
=IF( COLUMNS( V8:V8 )<=COUNTIF( $B$12:$F$2216,$T8 ),SMALL( IF( $B$12:$F$2216=$T8,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V8:V8 ) )1,"" )
V9
=IF( COLUMNS( V9:V9 )<=COUNTIF( $B$12:$F$2216,$T9 ),SMALL( IF( $B$12:$F$2216=$T9,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V9:V9 ) )1,"" )
V10
=IF( COLUMNS( V10:V10 )<=COUNTIF( $B$12:$F$2216,$T10 ),SMALL( IF( $B$12:$F$2216=$T10,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V10:V10 ) )1,"" )
V11
=IF( COLUMNS( V11:V11 )<=COUNTIF( $B$12:$F$2216,$T11 ),SMALL( IF( $B$12:$F$2216=$T11,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V11:V11 ) )1,"" )
V12
=IF( COLUMNS( V12:V12 )<=COUNTIF( $B$12:$F$2216,$T12 ),SMALL( IF( $B$12:$F$2216=$T12,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V12:V12 ) )1,"" )
V13
=IF( COLUMNS( V13:V13 )<=COUNTIF( $B$12:$F$2216,$T13 ),SMALL( IF( $B$12:$F$2216=$T13,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V13:V13 ) )1,"" )
V14
=IF( COLUMNS( V14:V14 )<=COUNTIF( $B$12:$F$2216,$T14 ),SMALL( IF( $B$12:$F$2216=$T14,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V14:V14 ) )1,"" )
V15
=IF( COLUMNS( V15:V15 )<=COUNTIF( $B$12:$F$2216,$T15 ),SMALL( IF( $B$12:$F$2216=$T15,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V15:V15 ) )1,"" )
V16
=IF( COLUMNS( V16:V16 )<=COUNTIF( $B$12:$F$2216,$T16 ),SMALL( IF( $B$12:$F$2216=$T16,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V16:V16 ) )1,"" )
V17
=IF( COLUMNS( V17:V17 )<=COUNTIF( $B$12:$F$2216,$T17 ),SMALL( IF( $B$12:$F$2216=$T17,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V17:V17 ) )1,"" )
V18
=IF( COLUMNS( V18:V18 )<=COUNTIF( $B$12:$F$2216,$T18 ),SMALL( IF( $B$12:$F$2216=$T18,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V18:V18 ) )1,"" )
V19
=IF( COLUMNS( V19:V19 )<=COUNTIF( $B$12:$F$2216,$T19 ),SMALL( IF( $B$12:$F$2216=$T19,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V19:V19 ) )1,"" )
V20
=IF( COLUMNS( V20:V20 )<=COUNTIF( $B$12:$F$2216,$T20 ),SMALL( IF( $B$12:$F$2216=$T20,ROW( $B$12:$F$2216 )ROW( $B$12 )+1 ),COLUMNS( V20:V20 ) )1,"" )
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
dear all,
I have a very heavy file with formula across several sheets (linked). I want to insert the formulas which are in a large area into a macro.
How can I insert a formula which will look for data in another sheet and sum them in another.
EX:
There is one sheet per employee which insert their daily task, the task are then compute per hour in another sheet. The weekly total is then computed in another sheet for all employees.
How can I write the formula which will look for the total of tasks per employee: it looks like
=if(iserror(countif(sheet1A1:A5,B1)/sumproduct(countif(sheet1A1:A5,LISTOFACTIVITIES))/1440*,"",countif(sheet1A1:A5,B1)/sumproduct(countif(sheet1A1:A5,LISTOFACTIVITIES))/1440*)
I think I am being pretty vague with giving the above formula...
but if someone could give some tips on how to write a similar formula in a macro for a given range of cell...
Looking forward to your answer.
Cheers
Bulke
Hi all
How can I copy cell sizes/column widths/Row Heights. I have created a worksheet with formulas and conditional formatting etc. I wish to have 1 workbook with 9 x worksheets the same. I've copied and pasted which copys formula and formats etc, but the column remain at the default worksheet width. I'm feeling a bit stupid, because I'm sure this is very basic stuff for you guys.
Thanks for any help.
Regards Simon
I have a worksheet that counts responses on another sheet drawn from a database. The columns on the database sheet do not line up exactly to the worksheet I'm trying to create the report on. So, I'm trying to do a lookup to match the formulas in the report grid to the columns on the data page. I know that doesn't make much sence right now, but here is the first formula in my "report" grid.
This is at D13: =COUNTIF(WTP!$P:$P,"Y").
This is at E13: =COUNTIF(WTP!$P:$P,"N").
This is at F13: =COUNTIF(WTP!:$P:$P,"N/A").
On the next row of the sheet the same formula counts the responses for the next question a couple of columns over:
D15: =COUNTIF(WTP!$R:$R,"Y")
E15: =COUNTIF(WTP!$R:$R,"Y")
F15: =COUNTIF(WTP!$R:$R,"Y")
This VLOOKUP tells me the column of the correct question, but I can't get the VLOOKUP formula to work inside the COUNTIF in place of
"WTP!$R:$R".....
=VLOOKUP(O13,$CG$3:$CH$95,2,FALSE)
I've copied all the columns labels from the data sheet and transposed them into column O next to the corresponding question on the report sheet. So, O13 in the VLOOKUP is next to the first question and contains the colum header label I'm looking for.
The reason I'm trying to do this is that the formula can't be "copied down" since every column does not contain a question and some rows contain just section titles instead of questions. Yeah, it's a mess, but it's what they want.
I'm trying to fix the formula so I can copy it down since there are 80 questions and 3 columns (Y,N,N/A) for each question. Otherwise I have to edit the formula in each cell manually to create it.
Anyway... I'm trying to plug this lookup:
=VLOOKUP(O13,$CG$3:$CH$95,2,FALSE)
which tells me the column of the question, into this formula:
=COUNTIF(WTP!$P:$P,"Y")
which counts the "Y" responses in that column.
I tried this:
=ADDRESS(1,VLOOKUP(O13,$CG$3:$CH$95,2,FALSE),4,1,"WTP")
with a copy of the column header labels in CG3:CG95, but it just returns "WTP!P1" and does not work in the COUNTIF formula. I need either something that will return WTP!P:P or the column referenced in some other way. The VLOOKUP returns "16", the correct column which is column P:P on page WTP.
Thanks for your assistance!
David
Hi All,
I've seen several times on this forum the sumproduct formula being used where times I thought you would use SUMIF.
Could some one please provide some examples of SUMPRODUCT and how it works as the help file is pretty poor and only provides a really basic example.
Any help would be appreciated...
How can I make this formula or make another formula that will not count blank cell but count cells that equal each other. (CELL I9)
Arizona
B
C
D
E
F
G
H
I
8
Arizona
Score
VS
Opponent
Score
Win
Loss
Tie
9
Cardinals
16
49ers
20
7
4
6
10
Cardinals
31
Jaguars
17
11
Cardinals
10
Colts
31
12
Cardinals
13
Cardinals
28
Texans
21
14
Cardinals
27
Seahawks
3
15
Cardinals
24
Giants
17
16
Cardinals
21
Panthers
34
17
Cardinals
41
Bears
21
18
Cardinals
31
Seahawks
20
19
Cardinals
21
Rams
13
20
Cardinals
17
Titans
20
21
Cardinals
Vikings
22
Cardinals
49ers
23
Cardinals
Lions
24
Cardinals
Rams
25
Cardinals
Packers
Spreadsheet Formulas
Cell
Formula
G9
=SUMPRODUCT(($C$9>$F$9))+SUMPRODUCT(($C$10>$F$10))+SUMPRODUCT(($C$11>$F$11))+SUMPRODUCT(($C$12>$F$12))+SUMPRODUCT(($C$13>$F$13))+SUMPRODUCT(($C$14>$F$14))+SUMPRODUCT(($C$15>$F$15))+SUMPRODUCT(($C$16>$F$16))+SUMPRODUCT(($C$17>$F$17))+SUMPRODUCT(($C$18>$F$18))+SUMPRODUCT(($C$19>$F$19))+SUMPRODUCT(($C$20>$F$20))+SUMPRODUCT(($C$21>$F$21))+SUMPRODUCT(($C$22>$F$22))+SUMPRODUCT(($C$23>$F$23))+SUMPRODUCT(($C$24>$F$24))+SUMPRODUCT(($C$25>$F$25))
H9
=SUMPRODUCT(($C$9<$F$9))+SUMPRODUCT(($C$10<$F$10))+SUMPRODUCT(($C$11<$F$11))+SUMPRODUCT(($C$12<$F$12))+SUMPRODUCT(($C$13<$F$13))+SUMPRODUCT(($C$14<$F$14))+SUMPRODUCT(($C$15<$F$15))+SUMPRODUCT(($C$16<$F$16))+SUMPRODUCT(($C$17<$F$17))+SUMPRODUCT(($C$18<$F$18))+SUMPRODUCT(($C$19<$F$19))+SUMPRODUCT(($C$20<$F$20))+SUMPRODUCT(($C$21<$F$21))+SUMPRODUCT(($C$22<$F$22))+SUMPRODUCT(($C$23<$F$23))+SUMPRODUCT(($C$24<$F$24))+SUMPRODUCT(($C$25<$F$25))
I9
=SUMPRODUCT(($C$9=$F$9))+SUMPRODUCT(($C$10=$F$10))+SUMPRODUCT(($C$11=$F$11))+SUMPRODUCT(($C$12=$F$12))+SUMPRODUCT(($C$13=$F$13))+SUMPRODUCT(($C$14=$F$14))+SUMPRODUCT(($C$15=$F$15))+SUMPRODUCT(($C$16=$F$16))+SUMPRODUCT(($C$17=$F$17))+SUMPRODUCT(($C$18=$F$18))+SUMPRODUCT(($C$19=$F$19))+SUMPRODUCT(($C$20=$F$20))+SUMPRODUCT(($C$21=$F$21))+SUMPRODUCT(($C$22=$F$22))+SUMPRODUCT(($C$23=$F$23))+SUMPRODUCT(($C$24=$F$24))+SUMPRODUCT(($C$25=$F$25))
Excel tables to the web >> Excel Jeanie HTML 4
Thank for your Help
I have workbook A that has 4 sheets with numerous formulas throughout. Workbook B is identical to A as far as the format. I copied and pasted numerous rows from A to B. I then went to B, Edit, link and tried to change the source from A to B. I keep getting "the formula is too long" box.
Any ideas on this? I'm lost!
i copied and pasted formulas from text
but now i gotta click the cell and then the green arrow beside the formula bar to activate each one
is there an easier way to do them all together
thank you
I have a basic calculation formula that calculated the date of work given to the date of completion and returns the difference, ie 14 days. =SUM(C11B11) if someone types text rather than a date it returns an error on the whole form.
Below that I have a formula =COUNTIF(D2:D13,"<=14")COUNTIF(D2:D13,"<1")*ISERROR(0) and =COUNTIF(D2:D13,"<=14")COUNTIF(D2:D13,"<1")*ISERROR(0) to calculate how many of the date ranges are within 14 days or out with 14 days.
I then break this down further by breaking this down by person
=SUMPRODUCT((A2:A13=A25),(D2:D13>14)) outwith timescale of a specific person where A25 is the persons name
=SUMPRODUCT((A2:A13=A25),(D2:D13<=14)) is within the timescale of 14 days.
I want to stop these calculations from sending back an error namely #Value! error if someone happens to enter some text in the date field.
I would love any help with this as my excel knowledge is limited
thankyou
I'm trying to determine the best way to have formulas that dynamically use the last row (either in a specific column(s) or on the entire sheet) as part of a range.
I know this is a not uncommon question and I've searched a lot and there seem to be numerous options but I'm still perplexed. One primary consideration is that I have a LOT of formulas (including complex arrays) in this sheet so I'm trying to do it as efficiently as possible.
As I see it, I have two options:
1. Determine the last row in Column A and use this in all formulas, even if other columns have less rows than column A.
2. Determine the last row of the specific column(s) I'm using in a formula.
Column A will always have the most rows in the sheet, will always be contiguous, and will always be either alpha or numeric. Other columns will be a bit more of a mixed bag.
Examples of the formulas I'm trying to "smarten up" are (i.e. I've hardcoded row 5000 in for now because that will always be larger than my data set):
=IF(ISNUMBER(Q2)=FALSE,"",RANK(Q2,Q$2:Q$5000,1)+COUNTIF(Q$2:Q2,Q2)1)
{=SUMPRODUCT(($L$2:$L$5000=L2),(E2>$E$2:$E$5000))+SUMPRODUCT(($E$2:E2=E2),($L$2:L2=L2))}
Suggestions or pointers?
I am tyring to learn some VBA code and how to code and wanted to know if this could be done. I have a Worksheet labeled "Master" in B6 it has a report start date which will change week to week. Currently I have it as 5/25/08. I have another sheet labeled Time Exclusion. The data comes from a different source that is copied and pasted into this sheet. The data will be pasted into the same locations everytime though. C4,H4,M4,R4,W4,AB4,AG4 all contain currently when pasted each day of the week. Sunday, Monday, Tuesday etc. What I would like to do is to have B6 in the Master sheet be used to change the Sunday, Monday etc. to the actual days in the week for the current report. So I was just going to use the formula for C4 =B6, and then H4 =C4+1 and then do that for the rest so that I would have 5/25/08 displayed for Sunday, 5/26/08 for Monday, etc. for each day of the week. The problem is when I copy and paste each time into the Time Exclusion Sheet it will remove those formulas. Is there a way that I can set this up using VBA so that anytime that the data is copied and pasted into the Time Exclusion Sheet that it will reformat the days into the actual days of the week?
Hi,
I have a spreadsheet which has numerous tabs with numerous rows in each all referencing another file/spreadsheet using sumif formulas. However the sumif formulas dont work when the other spreadsheet is closed (just returns the #value message. I understand from other threads that i should be able to replace my sumif formulas with a sumproduct and it will work regardless of the open or closed status of the other spreadsheet.
I have never used a sumproduct before and have spent a bit of time trying to change my sumif formula over and looked through various threads trying to find examples which match mine, but none seem quite the same. Is anyone able to help me. All the formulas that use the sumif are quite similar and are like this;
=SUMIF('C:\2010\Month end Reporting\g July Result\[management accounts.xlsx]Phased Actuals YTD'!$C$7:$Q$120,B7,'C:\2010\Month end Reporting\g July Result\[management accounts.xlsx]Phased Actuals YTD'!$L$7:$L$99)
Pardon my ignorance, i think it is just pregnant brain getting in the way of what should be quite straightforward! I hope someone can help.
With the below table I am using this Sumproduct formula in C22.
=SUMPRODUCT(($G$6:G13=$G$1),($D$6:D13>=1))
G1 is blank
TEST
D
E
G
6
$5,365.00
12/12/09
7
$7,745.00
8
$6,152.00
12/14/09
9
$5,365.00
10
$0.00
Spreadsheet Formulas
Cell
Formula
D6
=SUM(F6,H6,J6,L6)
G6
=IF(R6=0," ",R6)
D7
=SUM(F7,H7,J7,L7)
G7
=IF(R7=0," ",R7)
D8
=SUM(F8,H8,J8,L8)
D9
=SUM(F9,H9,J9,L9)
D10
=SUM(F10,H10,J10,L10)
G10
=IF(R10=0," ",R10)
Excel tables to the web >> http://www.exceljeaniehtml.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
The problem I am encountering is, because the date is brought into column G by a formula, it does not work.
I deleted the formula in G9 to test. And in my cell (C22)with the above formula I get 1, which would be right.
The answer that I am looking for should be 2, because I do need the formulas in G to bring in the date.
Help is very much appreciated.
Thanks

