Formual help needing using multiple criteria

[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

 

[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.

 

[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.

 

[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

 

[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

 

[Daniel.C]

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

 

[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.

 

[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.

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]

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

 

[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.

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
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

 

[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.

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.

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
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