Formulas Not Computing? 


Formulas Not Computing?  Excel 
View Answers 
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
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 Tutorials
How to Make Macros Run A LOT Faster
Here is a very simple and easytouse tip to make all of your Excel macros run A LOT faster. It is very simple and ...
Here is a very simple and easytouse 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 ...
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 ...
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
Quickly Convert Formulas into Their Output Values in Excel
This tutorial teaches you how to convert a formula or function into its displayed output in Excel. This is very im ...
This tutorial teaches you how to convert a formula or function into its displayed output in Excel. This is very im ...
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
 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
 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
 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
 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
 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 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.
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
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
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!
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 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
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
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?
=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 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!!
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 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
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'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(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
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!
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?
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.
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
=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
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 ...
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!
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
=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 04Jul08 46 19Dec08 9 14 11Jul08 46 30Dec08 9 15 18Jul08 45 02Jan09 11 16 25Jul08 42 14Jan09 9 17 01Aug08 37 0 18 08Aug08 38 0 19 15Aug08 34 0 20 22Aug08 45 0 21 29Aug08 35 0 22 05Sep08 26 0 23 12Sep08 30 0 24 19Sep08 24 0 25 26Sep08 20 0 26 03Oct08 24 0 27 10Oct08 28 0 28 17Oct08 29 0 29 24Oct08 26 0 30 31Oct08 27 0 31 07Nov08 25 0 32 14Nov08 23 0 33 21Nov08 19 0 34 28Nov08 17 0 35 05Dec08 16 0 36 12Dec08 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.exceljeaniehtml.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
_STATS
C D E F 11 TOTAL OUTSTANDING 12 13 04Jul08 46 19Dec08 9 14 11Jul08 46 30Dec08 9 15 18Jul08 45 02Jan09 11 16 25Jul08 42 14Jan09 9 17 01Aug08 37 0 18 08Aug08 38 0 19 15Aug08 34 0 20 22Aug08 45 0 21 29Aug08 35 0 22 05Sep08 26 0 23 12Sep08 30 0 24 19Sep08 24 0 25 26Sep08 20 0 26 03Oct08 24 0 27 10Oct08 28 0 28 17Oct08 29 0 29 24Oct08 26 0 30 31Oct08 27 0 31 07Nov08 25 0 32 14Nov08 23 0 33 21Nov08 19 0 34 28Nov08 17 0 35 05Dec08 16 0 36 12Dec08 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.exceljeaniehtml.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!
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
??
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
??