Adding a conditional to an odd/even query

[Pumperkin]

Hi,

I am trying to add a condition so that it will count odd/even references that are for today or earlier, referring to two other columns on another sheet.

When I enter it, it will not compute - not sure what I've got wrong.

=SUMPRODUCT((MOD(Unallocated!$B$2:$B$849,2)<>0)+0)

is the basic formula I have to count all odds in the column.

I want to add something like =IF('Sheet1!A:A<=TODAY() at the start, but it will not compute.

Any ideas?

 

[Pumperkin]

Hi,

I am trying to add a condition so that it will count odd/even references that are for today or earlier, referring to two other columns on another sheet.

When I enter it, it will not compute - not sure what I've got wrong.

=SUMPRODUCT((MOD(Unallocated!$B$2:$B$849,2)<>0)+0)

is the basic formula I have to count all odds in the column.

I want to add something like =IF('Sheet1!A:A<=TODAY() at the start, but it will not compute.

Any ideas?

To be more specific, full formula entered is:

=IF(Unallocated!C:C<=TODAY(), SUMPRODUCT((MOD(Unallocated!$B$2:$B$849,2)<>0)+0),"")

I get a #SPILL error, which I don't think I've ever seen before

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=SUMPRODUCT((Unallocated!$C$2:$C$849<=TODAY())*(MOD(Unallocated!$B$2:$B$849,2)<>0))

or

=COUNT(FILTER(Unallocated!$B$2:$B$849,(Unallocated!$C$2:$C$849<=TODAY())*(MOD(Unallocated!$B$2:$B$849,2)<>0)))