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
 
Ad

Advertisements

J

JLatham

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

Advertisements

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

Advertisements

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top