Countif multiple conditions

[Carlos_82]

Hi.

I have a table like this:

Name (A).....Date-in (B)....Date-out (C)
Name1........27/12/2008....3/01/2009
Name2........25/11/2008....28/11/2008
...

I want to count the rows (in the entire table) that B and C dates intersect with a reference dates (say J1 and J2).

It is a booking table so I want to know if the apartment is available for the reference dates (i.e. no bookings for that days).

I tried first a simple double conditional to know if a date is inside two dates but it didn't work:
=SUMPRODUCT(--(B:B<J1),--(C:C>J1))

Regards.

[arthurbr]

Hi and welcome to the board
Sumproduct does not work with entire columns like b:b
You must define the range as for example b1:b65535
Check this page for a detailed explanation

[Carlos_82]

i tried changing B:B by B1:B100 and it is the same.

Thanks.

[Carlos_82]

I tried changing B:B by B1:B100 and it is the same.

Thanks.

[arthurbr]

Could you post a sample of your data ( you of course should change both ranges and they need to be the same length)

[Carlos_82]

It worked in Excel with B:B (and C:C).

The problem is that I can't get it to work on Google Spreadsheets.


The "--" operator doesn't seem to exist in Calc (it is based in Openoffice Calc), so I tried this:
=SUMPRODUCT(IF((B:B<J14);1;0);IF((C:C>J14);1;0))

But the result is 0 when it should be 1, 2, ...

Thanks.

[JBeaucaire]

Your original post, you said column A needed to check for an intersect in J1 and J2, but your formulas are strictly checking J1. What happened to J2?

And it's not a single day you're checking, aren't you saying that the dates entered in J1:J2 represent a date range and you're comparing that entire date range against all the date ranges in B:C to see if there are any scheduling conflicts? So in J3 you'd want a result like ("Available", "Not Available").

[Carlos_82]

Your original post, you said column A needed to check for an intersect in J1 and J2, but your formulas are strictly checking J1. What happened to J2?

And it's not a single day you're checking, aren't you saying that the dates entered in J1:J2 represent a date range and you're comparing that entire date range against all the date ranges in B:C to see if there are any scheduling conflicts? So in J3 you'd want a result like ("Available", "Not Available").

Yes, the formula was only to test function. I don't have any problem with the formula.

What I want to do is to get it working on Openoffice Calc.

Thanks.

you try this formula

=SUMPRODUCT(--(B2:B50>=J1),--(C2:C50<=J2))  

That formula works on Excel but not in Openoffice Calc.

Any idea?

[tigertiger]

Hi.

I have a table like this:

Name (A).....Date-in (B)....Date-out (C)
Name1........27/12/2008....3/01/2009
Name2........25/11/2008....28/11/2008
...

I want to count the rows (in the entire table) that B and C dates intersect with a reference dates (say J1 and J2).

It is a booking table so I want to know if the apartment is available for the reference dates (i.e. no bookings for that days).

I tried first a simple double conditional to know if a date is inside two dates but it didn't work:
=SUMPRODUCT(--(B:B<J1),--(C:C>J1))

Regards.

you try this formula

=SUMPRODUCT(--(B2:B50>=J1),--(C2:C50<=J2))  

[arthurbr]

Carlos, this is an XL forum, not a Google spreadsheet nor an openoffice forum.
Please search for a forum for those applications
If you go on changing what you want to do every post and it has nothing to do with XL, I will have to close this thread.