#1
|
|||
|
|||
Formula Help Needed
Hi All
I have the following formula on a worksheet called "Metrics" however it is not calculating anything: SUMPRODUCT((Risks!K:K>=Y3)*(Risks!K:K<=Z3)) When I put all the values on the same sheet it work fine. Any help appreciated. Thanks Tony |
#2
|
|||
|
|||
Please attach your sheet. Or try a comma instead of an asterisk.
|
#3
|
|||
|
|||
Hi gebobs
Sample file attached. Basically I need to be able to calculate the number of "open" RED risks for each month of the year. Thanks in anticipation. Kind regards Tony |
#4
|
|||
|
|||
Maybe like shown in Metric!C15
|
#5
|
|||
|
|||
Hi Xor
Brilliant works perfectly. My only other query is to check for values in column Q that are >=3 and/or <=4. I have tried both "AND" and "OR" but to no avail: =SUMPRODUCT(ISNUMBER(Risks!$Q$5:$Q$20)*(AND(Risks! $Q5:$Q20>=3,Risks!$Q5:$Q20<=4)*(Risks!$K5:$K20>=(--("1/4/18")))*(Risks!$K5:$K20<=--("30/4/18")))) Thanks for your help so far. Tony |
#6
|
|||
|
|||
What about:
=SUMPRODUCT(ISNUMBER(Risks!Q5:Q20)*(Risks!$Q5:$Q20 >=3)*(Risks!$Q5:$Q20<=4)*(Risks!$K5:$K20>=(--("1/1/18")))*(Risks!$K5:$K20<=--("31/1/18"))) |
#7
|
|||
|
|||
Code:
=COUNTIFS(Risks!$K:$K,">= " & $Y$3,Risks!$K:$K,"<" & $AA$3+1) Code:
=COUNTIFS(Risks!$Q:$Q,">=" & 3,Risks!$Q:$Q,"<=" & 4,Risks!$K:$K,">= " & $Y$3,Risks!$K:$K,"<" & $AA$3+1) Code:
=DATEVALUE(1 & " " & U3 & " 2018") |
#8
|
|||
|
|||
Hi Xor
Thanks - works perfectly. Tony |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula needed; I'm helpless | Ylli | Excel | 10 | 12-13-2017 04:06 AM |
Help needed with IF formula with a Vlookup | jonnie_rc | Excel | 6 | 08-31-2017 01:27 PM |
Complicated Formula Needed | midgetmogalle | Excel | 14 | 01-29-2014 10:26 AM |
Formula help needed | Cosmo | Excel | 1 | 08-23-2012 11:50 AM |
Excel formula needed. | Rod | Excel | 1 | 08-03-2009 06:55 AM |