Four criteria MATCH INDEX lookup of date between matching two text

[John_J]

I got stuck after 2 criterias, can anyone help! I need a formula that returns
1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula
shall return 1 if the assembly workcell is occupied on the dates in
Sheet2!A2:A1000, and 0 if not.

For the formula in Sheet2!B2:
Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup
range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000
Criteria3: The text "Assembly". Lookup range Sheet1!P21000.
Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000

I have read several of the MATCH INDEX threads on this forum, but I cant get
tit to work with 4 criterias combined with the "between dates". Dates are
formated as dd.mm.yyyy. Text is formated as "General".

Sheet1: (Letters P, X, Y, AI are col. headings)
P X Y AI
Status code Cell name Start date Finish date
Assembly Cell1 01.10.2008 31.10.2008


Sheet2: (Results in B2 etc.)
A B C D
Running dates Cell1 Cell2 Cell3
30.09.2008 0 0 0
01.10.2008 1 0 0
02.10.2008 1 0 0

 

[David Biddulph]

Please don't talk about "2 criterias" and "4 criterias".
The singular is criterion, the plural is criteria.

Hopefully someone else can answer your question.

 

[John_J]

Thank you! Sorry - English is my second language.
--
Thanks
John_J

Please don't talk about "2 criterias" and "4 criterias".
The singular is criterion, the plural is criteria.

Hopefully someone else can answer your question.

 

[Roger Govier]

Hi John

As far as I can see you are only using 3 criteria on your Results page. You
don't seem to be using Assembly.
Anyway, I would first of all create some named ranges to make life simpler.
Insert>Name>Define>
Name Start Refers to =Sheet1!Y2:Y1000
Name Finish Refers to =Sheet2!AI2:AI1000
Name Cells Refers to =Sheet1!:X2:X1000
Name Assembly Refers to =Sheet1!P21000

In cell B2 of your Results sheet enter
=SUMPRODUCT((Start>=$A2)*(Finish(<=$A2)*(Cells=B$1))

Copy across and down as required

 

[Roger Govier]

=SUMPRODUCT((Start>=$A2)*(Finish(<=$A2)*(Cells=B$1))

Apologies, a surplus parenthesis crept in there.
It should read
=SUMPRODUCT((Start>=$A2)*(Finish<=$A2)*(Cells=B$1))

--
Regards
Roger Govier

Hi John

As far as I can see you are only using 3 criteria on your Results page.
You don't seem to be using Assembly.
Anyway, I would first of all create some named ranges to make life
simpler.
Insert>Name>Define>
Name Start Refers to =Sheet1!Y2:Y1000
Name Finish Refers to =Sheet2!AI2:AI1000
Name Cells Refers to =Sheet1!:X2:X1000
Name Assembly Refers to =Sheet1!P21000

In cell B2 of your Results sheet enter
=SUMPRODUCT((Start>=$A2)*(Finish(<=$A2)*(Cells=B$1))

Copy across and down as required

 

[David Biddulph]

In which case, congratulations! It's often difficult making oneself
understood in a technical discussion in one's first language, so you're
doing very well in your second.
--
David Biddulph

Thank you! Sorry - English is my second language.

 

[Roger Govier]

I might get it right eventually<bg>

You said you wanted a zero or 1, not the count.
I have also put in a check to prevent a false result if there is no date in
column A of your results sheet.
=IF($A2="","",IF(SUMPRODUCT((Start>=$A2)*
(Finish<=$A2)*(Cells=B$1))>0,1,0)


--
Regards
Roger Govier

=SUMPRODUCT((Start>=$A2)*(Finish(<=$A2)*(Cells=B$1))

Apologies, a surplus parenthesis crept in there.
It should read
=SUMPRODUCT((Start>=$A2)*(Finish<=$A2)*(Cells=B$1))

 

[John_J]

Roger,
Thank you for your advice.

I tested your suggestion but it retuned "0" all the way down and across.

I do need the forth criterion: "Assembly". If it read "Test" ore something
else I want the formula to return "0".

By the way, I didn't think it was possible to include text values in a
SUMPRODUCT formula. The cell name is text. So is the status code "Assembly".
That's why I was originally looking for a MATCH INDEX formula. I hope I'm
wrong here as I would prefer a SUMPRODUCT formula.

 

[NoodNutt]

G'day John

With regards to your comment responding to Roger

By the way, I didn't think it was possible to include text values in a
SUMPRODUCT formula. The cell name is text. So is the status code "Assembly".
That's why I was originally looking for a MATCH INDEX formula. I hope I'm
wrong here as I would prefer a SUMPRODUCT formula.

I use a a 4 Criteria SUMPRODUCT:

=SUMPRODUCT(--('2008'!$B$5:$B$2004="SYD"),--('2008'!$D$5:$D$2004="ADE"),--('2008'!$E$5:$E$2004="01.JAN"),--('2008'!$N$5:$N$2004="Accident"))

Works fine for what I use it for.

HTH
Mark.

 

[John_J]

Thank you Roger. I made a silly mistake. When I copied from this forum the
formula was entered in two lines in the formula bar. I just had to delete the
line break. To get the correct response I added the criterion "Assembly" and
reverted the "<" and ">". It now looks like this:

=IF($A2="";"";IF(SUMPRODUCT((Start<=$A2)*(Finish>=$A2)*(Status="Assembly")*(Cell=B$1))>0;1;0)

Replace ";" with "," for US region PC's etc.