Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Formulas Not Computing?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


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!


View Answers     

Similar Excel Tutorials

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

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


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,



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
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")

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:

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.
2. Then i made array formula in column H to get the position
Some improvement to write fail & position
And more work to get total marks & position and Fail at the same time......
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

Hope to have a quick & effective solution from experts.


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.

Usually i use countif functions to count the number of Y's for yes in cells based on teh criteria. Well it is very error prone. With hundreds of cells the cell ranges can get off and result in inaccurate data. Finding the problem can be tendious. Also, you can only add so many countif statements before Excel tells you that you have too many parameters.

So i thought i would use the sumproduct. BUt i can't get it to work the way i want.

Here is the formulas i was trying to use.

=SUM( SUMPRODUCT((A5:A578="Patient Name")*(B5:B578="Y") ),( SUMPRODUCT(A5:A578="Patient Name")*(C5:C578="Y") ),( SUMPRODUCT(A5:A578="Patient Name")*(D5:D578="Y") ),( SUMPRODUCT(A5:A578="Patient Name")*(E5:E578="Y") ),( SUMPRODUCT(A5:A578="Patient Name")*(F5:F578="Y") ))

Different variation that didn't work:

= ( SUMPRODUCT((A5:A578="Patient Name")*(B5:B578="Y") )+( SUMPRODUCT(A5:A578="Patient Name")*(C5:C578="Y") )+( SUMPRODUCT(A5:A578="Patient Name")*(D5:D578="Y") )+( SUMPRODUCT(A5:A578="Patient Name")*(E5:E578="Y") )+( SUMPRODUCT(A5:A578="Patient Name")*(F5:F578="Y") ))

Since I can't attach the file, I've provided this webpage with a jpg.

There are 20 pages for 200 charts. I thought the sumproduct might be easier than countif but doing it the old way may be my only option. I don't know.

I have multiple cells with long formulas that have fixed references $. I copied and pasted the formulas across rows very nicely. However, when I copy and paste the formulas down columns, the refererence cell needs to change also. Since the formulas are long, I need a way to click on the cell and hit a "magic" button(s) to clear only the $ signs out of the formulas so they can be pasted properly. I hope that makes sense. Thanks, Kenny

my table (below) has an error.
In C2:C9 should be the number of activities per category that have been finished before today.
The best would be to even include the option, if Activitiy Status = "closed"

A B C D E F G 1 Category # tasks burned down due completed % done left 2 Administration 2 #N/A 0 0 0% 2 3 Finance 0 #N/A 0 0 0% 0 4 IT 5 #N/A 0 0 0% 5 5 Marketing 0 #N/A 0 0 0% 0 6 Operations 0 #N/A 0 0 0% 0 7 HR 0 #N/A 0 0 0% 0 8 Property & Inventory 10 #N/A 1 1 10% 9 9 Sales 0 #N/A 0 0 0% 0

Worksheet Formulas Cell Formula B2 =COUNTIF( Activity_Project_Group,A2 ) C2 =SUMPRODUCT( ( Activity_Project_Group=A2 )*( Activity_Act.Finish

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?


  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 :


I and am trying to enter the following formulas on the same worksheet in adjacent columns:


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?


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?


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:

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

I have a worksheet that counts responses on another sheet drawn from a database. The columns on the database sheet do not line up exactly to the worksheet I'm trying to create the report on. So, I'm trying to do a lookup to match the formulas in the report grid to the columns on the data page. I know that doesn't make much sence right now, but here is the first formula in my "report" grid.
This is at D13: =COUNTIF(WTP!$P:$P,"Y").
This is at E13: =COUNTIF(WTP!$P:$P,"N").
This is at F13: =COUNTIF(WTP!:$P:$P,"N/A").

On the next row of the sheet the same formula counts the responses for the next question a couple of columns over:
D15: =COUNTIF(WTP!$R:$R,"Y")
E15: =COUNTIF(WTP!$R:$R,"Y")
F15: =COUNTIF(WTP!$R:$R,"Y")

This VLOOKUP tells me the column of the correct question, but I can't get the VLOOKUP formula to work inside the COUNTIF in place of

I've copied all the columns labels from the data sheet and transposed them into column O next to the corresponding question on the report sheet. So, O13 in the VLOOKUP is next to the first question and contains the colum header label I'm looking for.

The reason I'm trying to do this is that the formula can't be "copied down" since every column does not contain a question and some rows contain just section titles instead of questions. Yeah, it's a mess, but it's what they want.

I'm trying to fix the formula so I can copy it down since there are 80 questions and 3 columns (Y,N,N/A) for each question. Otherwise I have to edit the formula in each cell manually to create it.

Anyway... I'm trying to plug this lookup:
which tells me the column of the question, into this formula:
which counts the "Y" responses in that column.

I tried this:
with a copy of the column header labels in CG3:CG95, but it just returns "WTP!P1" and does not work in the COUNTIF formula. I need either something that will return WTP!P:P or the column referenced in some other way. The VLOOKUP returns "16", the correct column which is column P:P on page WTP.

Thanks for your assistance!

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)

  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


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):


Suggestions or pointers?

I am tyring to learn some VBA code and how to code and wanted to know if this could be done. I have a Worksheet labeled "Master" in B6 it has a report start date which will change week to week. Currently I have it as 5/25/08. I have another sheet labeled Time Exclusion. The data comes from a different source that is copied and pasted into this sheet. The data will be pasted into the same locations everytime though. C4,H4,M4,R4,W4,AB4,AG4 all contain currently when pasted each day of the week. Sunday, Monday, Tuesday etc. What I would like to do is to have B6 in the Master sheet be used to change the Sunday, Monday etc. to the actual days in the week for the current report. So I was just going to use the formula for C4 =B6, and then H4 =C4+1 and then do that for the rest so that I would have 5/25/08 displayed for Sunday, 5/26/08 for Monday, etc. for each day of the week. The problem is when I copy and paste each time into the Time Exclusion Sheet it will remove those formulas. Is there a way that I can set this up using VBA so that anytime that the data is copied and pasted into the Time Exclusion Sheet that it will re-format the days into the actual days of the week?

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.

G1 is blank


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

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 and just miss it from the calculation.

I would love any help with this as my excel knowledge is limited


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.


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