Sum values based on mulitple conditional statements with array formula

[bwill22]

Hello,

I'm trying to sum the values in a table based on several conditions being met and I'm having trouble getting the correct answer. The array formula in the attached file (see cell AA3) sums the values based upon the single condition that the hour is greater than or equal to 8. When I try to add the second condition using the AND formula, I'm getting #Value error (see cell AB3). The second condition is that Z3="On". Any help is greatly appreciated!

[jeffreybrown]

Not sure why you need an array formula when this would suffice >> =IF(Z3="On",SUMIF(B1:Y1,">=8",B3:Y3),"")

[Richard Buttrey]

Hi,

You don't need an array function.
You could use SUMIF

Please Login or Register  to view this content.

Then just preface it with an If Test

Please Login or Register  to view this content.

[bwill22]

Thanks Jeff! I suppose I was overthinking this and making it unnecessarily complex. Appreciate the assist!

Barry

[jeffreybrown]

Here's another option for the formula in column Z >> =IF(OR(WEEKDAY(A2,2)>5,ISNUMBER(MATCH(A2,$AH$4:$AH$9,0))),"Off","On")

[bwill22]

Appreciate the reply Richard. Overthinking something so simple!

[jeffreybrown]

You are very welcome Barry. We are happy to assist.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.