Formual help needing using multiple criteria
 

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

Formual help needing using multiple criteria
 

Can you explain this in some more detail?

You say you want to "formulate" the data in C19:C24 and D19:D24 based on the
information in A1:D4 - what exactly is it you want to do with C19:C24/D19:D24
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.

"Jeremy" wrote:

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

Formual help needing using multiple criteria
 

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.

"JLatham" wrote:

Can you explain this in some more detail?

You say you want to "formulate" the data in C19:C24 and D19:D24 based on the
information in A1:D4 - what exactly is it you want to do with C19:C24/D19:D24
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.

"Jeremy" wrote:

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

Formual help needing using multiple criteria
 

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)<=EQUI V($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 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

Formual help needing using multiple criteria
 

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

"Daniel.C" wrote:

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)<=EQUI V($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 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

Formual help needing using multiple criteria
 

Sorry, I put my data on row 7 instead of 19; Here is an example :
http://www.filedropper.com/jeremy
Daniel

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

"Daniel.C" wrote:

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)<=EQUI V($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 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

Formual help needing using multiple criteria
 

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" wrote:

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

"Daniel.C" wrote:

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)<=EQUI V($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 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

Formual help needing using multiple criteria
 

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" wrote:

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

"Daniel.C" wrote:

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)<=EQUI V($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
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

Formual help needing using multiple criteria
 

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


Sorry, I put my data on row 7 instead of 19; Here is an example :
http://www.filedropper.com/jeremy
Daniel

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

"Daniel.C" wrote:

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)<=EQUI V($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
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

Formual help needing using multiple criteria
 

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" wrote:

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

"Daniel.C" wrote:

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)<=EQUI V($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
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

Formual help needing using multiple criteria
 

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" wrote:

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" wrote:

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

"Daniel.C" wrote:

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)<=EQUI V($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
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