Formula Help Needed

OTPM · #1 02-01-2018, 06:29 AM

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

gebobs · #2 02-01-2018, 07:21 AM

Please attach your sheet. Or try a comma instead of an asterisk.

OTPM · #3 02-01-2018, 07:34 AM

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

xor · #4 02-01-2018, 11:12 AM

Maybe like shown in Metric!C15

OTPM · #5 02-05-2018, 02:13 AM

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

xor · #6 02-05-2018, 02:45 AM

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

ArviLaanemets · #7 02-05-2018, 04:58 AM

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)

You had a wrong reference for end of month. And the formula for start of month returned string instead of date, so no match was found. Replace the formula in Y3 with

Code:

=DATEVALUE(1 & " " & U3 & " 2018")

OTPM · #8 02-05-2018, 07:16 AM

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