| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple Sumif formual with Criteria | Excel Discussion (Misc queries) | |||
| match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
| Needing to return multiple values from single column | Excel Worksheet Functions | |||
| student needing help w/vlookup function grading criteria | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
