Problems with a Crazy Array

Originally Posted by gstarborn

I am trying to write an array formula that does the following:

• Checks that the date in column C is less than or equal to a given date, if so - takes the count (or sum, since it's an array) and multiplies it by 6
• If the date is not less than or equal to the specified date, it then checks that the date in column D is less than or equal to a given date, if so - takes the count (or sum, since it's an array) and multiplies it by 3
• If the date is not less than or equal to the specified date, it then checks that the date in column B is less than or equal to a given date, if so - takes the count (or sum, since it's an array) and multiplies it by 1
• And then sums the total point value

All the while I need to ignore the blanks. I've tried variations of the following:

=SUM(IF(AND($C$3:$C$11<>"",$C$3:$C$11<=B14)>0,6,IF(AND($D$3:$D$11<>"",$D$3:$D$11<=B14)>0,3,IF(AND($B$3:$B$11<>"",$B$3:$B$11<=B14)>0,1))))

=SUM(IF($C$3:$C$11<=$B14,((COUNT(IF($C$3:$C$11<=$B14,6)))-(COUNT(IF($C$3:$C$11="",1)))),IF($D$3:$D$11<=$B14,((COUNT(IF($D$3:$D$11<=$B14,3)))-(COUNT(IF($D$3:$D$11="",1)))),IF($B$3:$B$11<=$B14,((COUNT(IF($B$3:$B$11<=$B14,1)))-(COUNT(IF($B$3:$B$11="",1))))))))

It's not calculating correctly - I've summed this small example up manually and it's off.

Any help on this would be much appreciated! And yes, I am using CSE properly.

=sumproduct(--($C$1:$C$100<=date1)*6+--($D$1:$D$100<=date2)*3+--($B$1:$B$100<=date3))

that assumes you have 100 rows of data. modify as needed.
ensure that the number of rows of data for each of the three sections is equal or the formula will error. and obviously replace date1,2,3 with real dates

Close - but it's not ignoring the blanks!

Originally Posted by gstarborn

Close - but it's not ignoring the blanks!

sorry didn't see that part!

add a ---($C$1:$C$100="")*6
and the equivalent for D and B

not the prettiest, but probably the easiest from where we are

Yep - did that. But it's not registering that if C has a value, dont check column D or B.

Originally Posted by gstarborn

Yep - did that. But it's not registering that if C has a value, dont check column D or B.

Try this one... sort of complicated. I think there must be something simpler, but this has one benefit, it should work. :-P (I hope. :-D)

=SUM(IF(C3:C11="",IF(D3:D11="",IF(B3:B11="",0,IF(B3:B11<=$B$14,1,0)),IF(D3:D11<=$B$14,3,IF(B3:B11="",0,IF(B3:B11<=$B$14,1,0)))),IF(C3:C11<=$B$14,6,IF(D3:D11="",IF(B3:B11="",0,IF(B3:B11<=$B$14,1,0)),IF(D3:D11<=$B$14,3,IF(B3:B11="",0,IF(B3:B11<=$B$14,1,0)))))))

Scott

Originally Posted by Maistrye

Try this one... sort of complicated. I think there must be something simpler, but this has one benefit, it should work. :-P (I hope. :-D)

=SUM(IF(C3:C11="",IF(D3:D11="",IF(B3:B11="",0,IF(B3:B11<=$B$14,1,0)),IF(D3:D11<=$B$14,3,IF(B3:B11="",0,IF(B3:B11<=$B$14,1,0)))),IF(C3:C11<=$B$14,6,IF(D3:D11="",IF(B3:B11="",0,IF(B3:B11<=$B$14,1,0)),IF(D3:D11<=$B$14,3,IF(B3:B11="",0,IF(B3:B11<=$B$14,1,0)))))))

Scott

wow. that's ugly. jk

thanks scott