If cells D4 to D87 have values that range from 45% to 100%, how do i put a formula in E78 that counts the number of cells with values <80% and >=65%?
2006-11-19 21:18:02 · 6 answers · asked by mads 1 in Computers & Internet ➔ Software
Create a new column in F with formula :
"=IF(D4>=65,IF(D4<80,1,0),0)"
Propogate this formula (Ctrl + Drag with mouse) for rows 4 to 87 in column F. Finally, insert a formula in E78 "=SUM(F4:F87)". This number will give you the number of cells in D4 to D87 that fits the criteria.
2006-11-19 21:32:10 · answer #1 · answered by Dewdrop 3 · 1⤊ 0⤋
Instead of all these trash, use this
- In E4 type the name of col D (say it is "Qty")
- In E5 type this <80%
- In E6 type this >=65%
- Now in E78 paste this
=DCOUNT( D4:D87, "Qty", E4:E6)
this will use the criteria in E4:E6 to count the number of cells in D4:D87 that maches
"Qty" is the name of the column of D (Used to be in D3)
mail me any further question, if you are interesting
Enjoy my profile, I am the VBAXLMan
2006-11-20 00:41:06 · answer #2 · answered by Anonymous · 2⤊ 0⤋
1. Press [Alt]F11.
2. Enter the following code at the prompt:
Function COUNTBETWEEN(rng, num1, num2)
COUNTBETWEEN = Application.CountIf(rng, "<=" &
num2) - Application.CountIf(rng, "<" & num1)
End Function
3. Press [Alt]F11.
To use the function, enter the following formula in a blank cell:
=COUNTBETWEEN(D4:D87,65,79)
2006-11-19 21:54:02 · answer #3 · answered by Inquisitive 2 · 0⤊ 0⤋
Enter either of the following in cell E78
=COUNTIF(D4:D87,">="&65%)-COUNTIF(D4:D87,">="&80%)
or
=SUMPRODUCT((D4:D87>=65%)*(D4:D87<=80%))
2006-11-19 23:11:15 · answer #4 · answered by bluegenel 2 · 1⤊ 0⤋
you should provide specifics besides. in case you want to calculate the adaptation between 2 dates, then you actually can use this formula: =IF(DATEDIF(A2, B2, "y")=0, "", DATEDIF(A2, B2, "y")&" years ")&IF(DATEDIF(A2, B2, "ym")=0, "", DATEDIF(A2, B2, "ym")&" months ")&DATEDIF(A2, B2, "md")+a million&" days" A2 is the first date B2 is the present date
2016-10-16 09:45:19 · answer #5 · answered by lubin 4 · 0⤊ 0⤋
You must provide specifics anyway. If you want to calculate the change between 2 dates, then you should utilize this method: =IF(DATEDIF(A2, B2, "y")=0, "", DATEDIF(A2, B2, "y")&" years ")&IF(DATEDIF(A2, B2, "ym")=0, "", DATEDIF(A2, B2, "ym")&" months ")&DATEDIF(A2, B2, "md")+1&" days" A2 is the first date B2 is the trendy date
2016-08-09 22:59:24 · answer #6 · answered by kindle 4 · 0⤊ 0⤋