Sumproduct Arrays And / OR

Hi,

I am creating a Sumproduct fromular with many different conditions,

I have noticed that you can put "+" "--," or "," or even, ",+" between the conditions or arrays.

I am getting a bit confused as to which one to use, my formular is error free and returning 0 or gives a value if i change a few about.

Basically i am trying to get the sum, where the time of any events are on a weekday and between 18:00pm and 04:00am

My formular therefore needs to say in simple terms.... if monday or tuesday or wednesday or thursday or friday then add up the data where it occurs between 18:00 and 04:00

What seperators should i use between the arrays to achieve this?

Many thanks in advance

It depends a little how your data is set up, do you have date in one column and time in another or both in the same column?

Assuming date in A2:A100, time in B2:B100 and amounts in C2:C100 you could use this formula

=SUMPRODUCT((WEEKDAY(A2:A100,2)<6)+0,(B2:B100>="18:00"+0)+(B2:B100<="4:00"+0),C2:C100)

....or do you just have text "Monday" etc.?

In general, in a "normal" SUMPRODUCT function all the conditions have to be true so if you have this

=SUMPRODUCT(--(A2:A10="x"),--(B2:B10="y"),C2:C10)

then that will sum C when A is "x" and B is "y"

If you want to expand that to count when B2:B10 is either "y" or "z" you could expand like this:

=SUMPRODUCT(--(A2:A10="x"),(B2:B10="y")+(B2:B10="z"),C2:C10)

Note that the + effectively gives you an "OR" [ and the + also acts as a "coercer" so you don't need -- for that part now]

but if you have multiple options for B2:B10 it might be simpler to use this syntax

=SUMPRODUCT(--(A2:A10="x"),--ISNUMBER(MATCH(B2:B10={"v","w","y","z"},0)),C2:C10)

combining those for your formula you can try

SUMPRODUCT(--(January!$D$1:$D$1000=$A18),--ISNUMBER(MATCH(January!$I$1:$I$1000,{"Monday","Tuesday","Wednesday","Thursday","Friday"},0)), (January!$H$1:$H$1000>="17:00"+0)+(January!$H$1:$H$1000<="04:00"+0),January!$S$1:$S$1000)

Note that for the times to be recognised as times you need to "coerce" them, i.e. I used "17:00"+0 [or you can use TIME function like =TIME(17,0,0) ]

Note that when you use + you need to be careful that you don't double count if the items added can both (or all) be true. In this case the time can't be simultaneously >= 17:00 and <= 04:00 so you are OK, but if you were adding two conditions that could both be true you need to adopt this type of syntax

=SUMPRODUCT(--(A2:A10="x"),--((B2:B10=Z1)+(B2:B10=Z2)>0),C2:C10)

Thanks so much, great write up too, has helped me understand much better!

Note that if you have a date column it's actually easier to check whether the date is a weekday or not rather than checking the text day column, i.e. if date is in column G try

=SUMPRODUCT(--(January!$D$1:$D$1000=$A18),--(WEEKDAY(January!$G$1:$G$1000,2)<6), (January!$H$1:$H$1000>="17:00"+0)+(January!$H$1:$H$1000<="04:00"+0),January!$S$1:$S$1000)

...although note that if row 1 has text headers then WEEKDAY function applied to G1 will give an error and cause the formula to fail so you might want to adjust all ranges to start at row 2 in that case.....