# Formual help needing using multiple criteria

J

#### Jeremy

I am looking for a way to formulate the data in C19-24 as well as D19-24
using the information in A,B,C and D 1-4. Please note it is counting the
data including the months given and the months between. Can someone please
help with a
formula.

Thank you

A B C D
1 JAN JUNE 2 4
2 MAR JUNE 1 16
3 MAR MAR 2 2
4 FEB MAR 3 4

A B C D
19 JAN 2 4
20 FEB 5 8
21 MAR 8 26
22 APRIL 3 20
23 MAY 3 20
24 JUNE 3 20

J

#### JLatham

Can you explain this in some more detail?

You say you want to "formulate" the data in C19:C24 and D1924 based on the
information in A14 - what exactly is it you want to do with C19:C24/D1924
and how do you use the information in A-D:1-4 to determine what to do with it?

Perhaps even give us a couple of expected results.

J

#### Jeremy

What I am trying to do is take the data in the first list and formulate in
the second list by if the data in C or D 1 to 4 is on or between the months
in a and b it is counted in the second list.

D

#### Daniel.C

For C19, try the following array formula :
(validate with Ctrl+Shift+Enter)
=SUM(IF(MATCH(A7,\$A\$7:\$A\$12,0)>=MATCH(\$A\$1:\$A\$4,\$A\$7:\$A\$12,0),1,0)*IF(MATCH(A7,\$A\$7:\$A\$12,0)<=MATCH(\$B\$1:\$B\$4,\$A\$7:\$A\$12,0),1,0)*\$C\$1:\$C\$4)
For D19 :
=SOMME(SI(EQUIV(A7;\$A\$7:\$A\$12;0)>=EQUIV(\$A\$1:\$A\$4;\$A\$7:\$A\$12;0);1;0)*SI(EQUIV(A7;\$A\$7:\$A\$12;0)<=EQUIV(\$B\$1:\$B\$4;\$A\$7:\$A\$12;0);1;0)*\$D\$1:\$D\$4)
Drag down as needed.
HTH
Daniel

J

#### Jeremy

I must be doing something wrong because I can't seem to get this to pull
anything. Why is there are reference to A7 and a7 has nothing in it?

Thank you

J

#### JLatham

Also, he's working with French version, but I think the translation is fairly
obvious.

One big thing - as he mentioned, it is an 'array formula' and must be
finished up with [Ctrl]+[Shift]+[Enter] instead of just the [Enter] key. And
if you have to edit the formula(s), you have to end them in that same way for
them to continue to work.

D

#### Daniel.C

Thought the formulae were automaticaly translated ?
Daniel
Also, he's working with French version, but I think the translation is fairly
obvious.

One big thing - as he mentioned, it is an 'array formula' and must be
finished up with [Ctrl]+[Shift]+[Enter] instead of just the [Enter] key. And
if you have to edit the formula(s), you have to end them in that same way for
them to continue to work.

Jeremy said:
I must be doing something wrong because I can't seem to get this to pull
anything. Why is there are reference to A7 and a7 has nothing in it?

Thank you

D

#### Daniel.C

Oups. The formulae are :
=SUM(IF(MATCH(A7,\$A\$7:\$A\$12,0)>=MATCH(\$A\$1:\$A\$4,\$A\$7:\$A\$12,0),1,0)*IF(MATCH(A7,\$A\$7:\$A\$12,0)<=MATCH(\$B\$1:\$B\$4,\$A\$7:\$A\$12,0),1,0)*\$C\$1:\$C\$4)
and
=SUM(IF(MATCH(A7,\$A\$7:\$A\$12,0)>=MATCH(\$A\$1:\$A\$4,\$A\$7:\$A\$12,0),1,0)*IF(MATCH(A7,\$A\$7:\$A\$12,0)<=MATCH(\$B\$1:\$B\$4,\$A\$7:\$A\$12,0),1,0)*\$D\$1:\$D\$4)
Daniel

D

#### Daniel.C

Sorry. Forget that.
Daniel
Thought the formulae were automaticaly translated ?
Daniel
Also, he's working with French version, but I think the translation is
fairly obvious.

One big thing - as he mentioned, it is an 'array formula' and must be
finished up with [Ctrl]+[Shift]+[Enter] instead of just the [Enter] key.
And if you have to edit the formula(s), you have to end them in that same
way for them to continue to work.

Jeremy said:
I must be doing something wrong because I can't seem to get this to pull
anything. Why is there are reference to A7 and a7 has nothing in it?

Thank you

:

For C19, try the following array formula :
(validate with Ctrl+Shift+Enter)
=SUM(IF(MATCH(A7,\$A\$7:\$A\$12,0)>=MATCH(\$A\$1:\$A\$4,\$A\$7:\$A\$12,0),1,0)*IF(MATCH(A7,\$A\$7:\$A\$12,0)<=MATCH(\$B\$1:\$B\$4,\$A\$7:\$A\$12,0),1,0)*\$C\$1:\$C\$4)
For D19 :
=SOMME(SI(EQUIV(A7;\$A\$7:\$A\$12;0)>=EQUIV(\$A\$1:\$A\$4;\$A\$7:\$A\$12;0);1;0)*SI(EQUIV(A7;\$A\$7:\$A\$12;0)<=EQUIV(\$B\$1:\$B\$4;\$A\$7:\$A\$12;0);1;0)*\$D\$1:\$D\$4)
Drag down as needed.
HTH
Daniel

I am looking for a way to formulate the data in C19-24 as well as D19-24
using the information in A,B,C and D 1-4. Please note it is counting
the data including the months given and the months between. Can someone

Thank you

A B C D
1 JAN JUNE 2 4 2 MAR JUNE
1 16
3 MAR MAR 2 2
4 FEB MAR 3 4

A B C D
19 JAN 2 4
20 FEB 5 8
21 MAR 8 26
22 APRIL 3 20
23 MAY 3 20
24 JUNE 3 20

J

#### JLatham

I was referring to them as displayed in your post. I actually don't know if
they auto-translate or now (never tried, guess I could now that I have your
examples). But I was thinking just of the appearance in the posting rather
than actual functioning later.

Daniel.C said:
Thought the formulae were automaticaly translated ?
Daniel
Also, he's working with French version, but I think the translation is fairly
obvious.

One big thing - as he mentioned, it is an 'array formula' and must be
finished up with [Ctrl]+[Shift]+[Enter] instead of just the [Enter] key. And
if you have to edit the formula(s), you have to end them in that same way for
them to continue to work.

Jeremy said:
I must be doing something wrong because I can't seem to get this to pull
anything. Why is there are reference to A7 and a7 has nothing in it?

Thank you

:

For C19, try the following array formula :
(validate with Ctrl+Shift+Enter)
=SUM(IF(MATCH(A7,\$A\$7:\$A\$12,0)>=MATCH(\$A\$1:\$A\$4,\$A\$7:\$A\$12,0),1,0)*IF(MATCH(A7,\$A\$7:\$A\$12,0)<=MATCH(\$B\$1:\$B\$4,\$A\$7:\$A\$12,0),1,0)*\$C\$1:\$C\$4)
For D19 :
=SOMME(SI(EQUIV(A7;\$A\$7:\$A\$12;0)>=EQUIV(\$A\$1:\$A\$4;\$A\$7:\$A\$12;0);1;0)*SI(EQUIV(A7;\$A\$7:\$A\$12;0)<=EQUIV(\$B\$1:\$B\$4;\$A\$7:\$A\$12;0);1;0)*\$D\$1:\$D\$4)
Drag down as needed.
HTH
Daniel

I am looking for a way to formulate the data in C19-24 as well as D19-24
using the information in A,B,C and D 1-4. Please note it is counting the
data including the months given and the months between. Can someone
formula.

Thank you

A B C D
1 JAN JUNE 2 4
2 MAR JUNE 1 16
3 MAR MAR 2 2
4 FEB MAR 3 4

A B C D
19 JAN 2 4
20 FEB 5 8
21 MAR 8 26
22 APRIL 3 20
23 MAY 3 20
24 JUNE 3 20