Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Formulas Not Computing?

Forum Register
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 de-activate some default settings; as I mostly think it is tied to the 'Cell Category' to something non-standard, 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

View Answers     

Similar Excel Tutorials

Show All Formulas in a Worksheet in Excel
Display all formulas instead of their output values. This allows you to quickly troubleshoot issues in a worksheet ...
How to Make Macros Run A LOT Faster
Here is a very simple and easy-to-use tip to make all of your Excel macros run A LOT faster.  It is very simple and ...
Display all Formulas at Once in Excel
How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsheet quickly and easily. S ...
Stop Formula Calculation in Excel - Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...

Helpful Excel Macros

Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe

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 run-time 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 extra-ordinary solution like I am looking for a solution in a single column G:

http://www.excelforum.com/excel-gene...-in-array.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 374-2 4 JANNY 90 60 60 90 60 360 360-3 5 Gaughan 0 0 0 0 0 Fail Fail 6 Madona 75 75 80 65 80 375 375-1 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




I created a template to break down monthly expenditures for an NGO I volunteer with. The data is on one worksheet and then I have a worksheet for each month with total expenditures. I copied and pasted the formulas and every month but August works fine. I am totally baffled by why August won't work since the formulas were copied and pasted with me only changing the section of the formula for the month. My guess is that something is different about the data entry for August, but no mater how much I look at it I can't figure out anything that is different than the other months. I am a very novice excel user and the answer would probably be obvious to someone who uses excel a lot so I'm asking for suggestions about what can be different about the data entry, or cell formatting that would cause the formula to not work. I don't get an error message, it just isn't calculating the data.
Thanks!
Heather

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!


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   Logged-in 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


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?


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?


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!!




I would like to make these formulas a more 'user friendly' since the data is dynamic from day to day.

My formulas a
0: =SUMPRODUCT((B2:B246<>"")/COUNTIF(B2:B246,B2:B246&""))
1: =SUMPRODUCT((B247:B2242<>"")/COUNTIF(B247:B2242,B247:B2242&""))
2: =SUMPRODUCT((B2243:B4763<>"")/COUNTIF(B2243:B4763,B2243:B4763&""))
3: =SUMPRODUCT((B4764:B8216<>"")/COUNTIF(B4764:B8216,B4764:B8216&""))
4: =SUMPRODUCT((B8217:B8320<>"")/COUNTIF(B8217:B8320,B8217:B8320&""))
5: =SUMPRODUCT((B8321:B8322<>"")/COUNTIF(B8321:B8322,B8321:B8322&""))

I'm counting the unique values in column B where column K has a 0, 1, 2, 3, 4, or 5.
The data I want to do the uniques count on will always start in B2, but one day there will be a 0 in column K down to row B246, K = 1 from row B247:B2242, K = 2 from row B2243:B4763, etc, then next day K = 0 from row B2:B432, K = 1 from B433:B3200, etc

How can I change format of these formulas to autmatically find the dynamic range of 0, 1, 2, 3, 4, 5 without manually having to update the cell values in the formulas each day?

Just because there is more than one way to find the count of unique values, I can also use these array formulas:
{=SUM(1/COUNTIF(B2:B246,B2:B246))}
{=SUM(1/COUNTIF(B247:B2242,B247:B2242))}
{=SUM(1/COUNTIF(B2243:B4763,B2243:B4763))}
{=SUM(1/COUNTIF(B4764:B8216,B4764:B8216))}
{=SUM(1/COUNTIF(B8217:B8320,B8217:B8320))}
{=SUM(1/COUNTIF(B8321:B8322,B8321:B8322))}

So the same question here, How can I make these work with a dynamic range each day without manually entering the range?

Thanks in Advance
Don

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


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


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...


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(C11-B11) 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 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'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?


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.excel-jeanie-html.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


Hi folks,

I've got three columns of names and I need a formula to count the number of instances where any two names appear in any row.

I've tried using both COUNTIF and SUMPRODUCT but I can't get the right result when trying to use multiple column arrays in the formula.

e.g.

Apple Banana Cherry
Banana Orange Apple
Banana Lemon Grape
Lemon Apple Onion

My attempt at a formula to return the number of instances where 'Apple' and 'Banana' appear in the same row:

{=COUNT(IF((A1:C4="Apple")*(A1:C4="Banana"),D1:D4))} returns 0

{=SUMPRODUCT((A1:C4="Apple")*(A1:C4="Banana"))} also returns 0

I need something to return the result of 2. The only thing I can think of is to use multiple COUNTIF or SUMPRODUCT formulas to return the results over each combination of columns, and then sum the result.

There must be an easier way ...


Hi,

This is driving me crazy and it would be greatly appreciated if someone could help me out.

I want to sum the values in column M where the corresponding value in column F = 6 and the value in column E = E33. The formula I have come up with is below:

=SUMPRODUCT(($F$1:F5000=6)*($E$1:E5000=E30),$M$1:M5000)

However since I want the answer pasted in column M, this gives me a circular reference. Note that I want the answer to be copied into cell M30 and that F30 in is not equal to 6.

To avoid this I tried the formula

=SUM(SUMPRODUCT(($E$1:E29=E30)*($F$1:F29=6),$M$1:M29),SUMPRODUCT((E31:$E$5000=E30)*(F31:$F$5000=6),M31:$M$5000))

This works but the problem is if I try to paste it into another cell in column M, say M45 (again the value of F45 is not equal to 6) I get a circular reference problem again (between cells M45 and M30).

Is there a way I can resolve this? i.e. i need excel to only run the sum product on the cells within column M where the sumproduct conditions have been met and ignore the others thereby avoiding a circular reference.

I hope that makes sense!


Hi Iam using this formula i want to check for 3 criterion I am using:

=SUMPRODUCT(COUNTIF(D3:F3,{"1","2","3",4}))

which works fine

but want to know if it is possible to then add more countifs e.g

(COUNTIF(D3:F3,{"5","6","7",8})) and (COUNTIF(D3:F3,{"9","10","11",12}))

I have 12 possible marks that I want to split into 3 grades
does this make sense??

Thanks in advance


Hi is it possible to capture data range in Col D and F, i cant put data in Col F under data in Col D since it will strertch to far down.

_STATS

C D E F 11 TOTAL OUTSTANDING 12 13 04-Jul-08 46 19-Dec-08 9 14 11-Jul-08 46 30-Dec-08 9 15 18-Jul-08 45 02-Jan-09 11 16 25-Jul-08 42 14-Jan-09 9 17 01-Aug-08 37 0 18 08-Aug-08 38 0 19 15-Aug-08 34 0 20 22-Aug-08 45 0 21 29-Aug-08 35 0 22 05-Sep-08 26 0 23 12-Sep-08 30 0 24 19-Sep-08 24 0 25 26-Sep-08 20 0 26 03-Oct-08 24 0 27 10-Oct-08 28 0 28 17-Oct-08 29 0 29 24-Oct-08 26 0 30 31-Oct-08 27 0 31 07-Nov-08 25 0 32 14-Nov-08 23 0 33 21-Nov-08 19 0 34 28-Nov-08 17 0 35 05-Dec-08 16 0 36 12-Dec-08 11 0
Spreadsheet Formulas Cell Formula D13 =COUNTIF(NRFFDATE,C13) F13 =COUNTIF(NRFFDATE,E13) D14 =COUNTIF(NRFFDATE,C14) F14 =COUNTIF(NRFFDATE,E14) D15 =COUNTIF(NRFFDATE,C15) F15 =COUNTIF(NRFFDATE,E15) D16 =COUNTIF(NRFFDATE,C16) F16 =COUNTIF(NRFFDATE,E16) D17 =COUNTIF(NRFFDATE,C17) F17 =COUNTIF(NRFFDATE,E17) D18 =COUNTIF(NRFFDATE,C18) F18 =COUNTIF(NRFFDATE,E18) D19 =COUNTIF(NRFFDATE,C19) F19 =COUNTIF(NRFFDATE,E19) D20 =COUNTIF(NRFFDATE,C20) F20 =COUNTIF(NRFFDATE,E20) D21 =COUNTIF(NRFFDATE,C21) F21 =COUNTIF(NRFFDATE,E21) D22 =COUNTIF(NRFFDATE,C22) F22 =COUNTIF(NRFFDATE,E22) D23 =COUNTIF(NRFFDATE,C23) F23 =COUNTIF(NRFFDATE,E23) D24 =COUNTIF(NRFFDATE,C24) F24 =COUNTIF(NRFFDATE,E24) D25 =COUNTIF(NRFFDATE,C25) F25 =COUNTIF(NRFFDATE,E25) D26 =COUNTIF(NRFFDATE,C26) F26 =COUNTIF(NRFFDATE,E26) D27 =COUNTIF(NRFFDATE,C27) F27 =COUNTIF(NRFFDATE,E27) D28 =COUNTIF(NRFFDATE,C28) F28 =COUNTIF(NRFFDATE,E28) D29 =COUNTIF(NRFFDATE,C29) F29 =COUNTIF(NRFFDATE,E29) D30 =COUNTIF(NRFFDATE,C30) F30 =COUNTIF(NRFFDATE,E30) D31 =COUNTIF(NRFFDATE,C31) F31 =COUNTIF(NRFFDATE,E31) D32 =COUNTIF(NRFFDATE,C32) F32 =COUNTIF(NRFFDATE,E32) D33 =COUNTIF(NRFFDATE,C33) F33 =COUNTIF(NRFFDATE,E33) D34 =COUNTIF(NRFFDATE,C34) F34 =COUNTIF(NRFFDATE,E34) D35 =COUNTIF(NRFFDATE,C35) F35 =COUNTIF(NRFFDATE,E35) D36 =COUNTIF(NRFFDATE,C36) F36 =COUNTIF(NRFFDATE,E36)

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


There has to be an easier way than the code I have typed.

I have to total the number of blank cells and 5 other pieces of data together that meets the criteria of "M/", which is located in column C:

These are daily values in a 28 day fiscal calendar, hence the $C in the M/ statement.

Data (Blank Cells and LD1, LD2, LD3, LD4, LDO)

I have pasted the formula below:

This represents the blank cells.
=SUMPRODUCT(--(ISNUMBER(FIND("M/",$C17:$C96))),--(I17:I96=""))
This part is the LD cells
+SUMPRODUCT(--(ISNUMBER(FIND("M/",$C17:$C96))),--(I17:I96="LD1"))
+SUMPRODUCT(--(ISNUMBER(FIND("M/",$C17:$C96))),--(I17:I96="LD2"))
+SUMPRODUCT(--(ISNUMBER(FIND("M/",$C17:$C96))),--(I17:I96="LD3"))
+SUMPRODUCT(--(ISNUMBER(FIND("M/",$C17:$C96))),--(I17:I96="LD4"))
+SUMPRODUCT(--(ISNUMBER(FIND("M/",$C17:$C96))),--(I17:I96="LDO"))
This is the CountIf part which works fine.
+COUNTIF(I17:I96,"/")

The formula has been input as 1 long statement in the formula bar, and it works, but I think it's sloppy, and would like to find a more efficient solution.

Thank you!


Hi all. Been producing some Ranks in excel.

When a position occupied an equal rank with another, they are both given the same refererence, e.g. 3rd and 3rd, with the next rank being 5th (the 4th is ignored)

To produce true rank I have to use a countif. Looking for a nice all in one formula to ignore the helper column. Using an array, sumproduct, frequency formula...help please!

[Range H2 : J7 shown below]

OVERALL RANK TRUE 30.21% 2 2 58.11% 1 1 5.81% 3 3 5.81% 3 4 0.06% 5 5

Formula to produce auto ranking is placed in J3 and copied down

=IF(COUNTIF($I$2:$I2,$I3)>0,$I3+COUNTIF($I$2:$I2,$I3),$I3)

Want something more elegant so I can get rid of the RANK column

??