| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountIf embedded in an IF statement
Good Morning,
I have a situation where I would like to count the number of orders created by a certain person within a specific month. What I would like to say is Count the number of orders that were placed in the month of June for XIAPPLXRP110 Here is the formula I was trying to use which obviously did not work. =IF('Raw Data'!a2:a14000(left,A2,2,=6/),COUNTIF('Raw Data'!B:B,"XIAPPLXRP110"),"") Here is some sample data below. Date Creator Order # 6/5/2007 SCHMINKN 427998 7/24/2007 SCHMINKN 427954 6/27/2007 XIAPPLXRP110 428031 7/2/2007 XIAPPLXRP110 427985 6/18/2007 PATTEEA 427947 7/8/2007 PATTEEA 427944 HELP Please. I am trying to do these myself but I am struggling. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIf embedded in an IF statement
already replied to your previous posting ... be patient!
=SUMPRODUCT(--(MONTH(A2:A100)=X1),--(B2:B100=X2)) X1=6 (June) (assume all in same year) X2=XIAPPLXRP110 "Amber" wrote: Good Morning, I have a situation where I would like to count the number of orders created by a certain person within a specific month. What I would like to say is Count the number of orders that were placed in the month of June for XIAPPLXRP110 Here is the formula I was trying to use which obviously did not work. =IF('Raw Data'!a2:a14000(left,A2,2,=6/),COUNTIF('Raw Data'!B:B,"XIAPPLXRP110"),"") Here is some sample data below. Date Creator Order # 6/5/2007 SCHMINKN 427998 7/24/2007 SCHMINKN 427954 6/27/2007 XIAPPLXRP110 428031 7/2/2007 XIAPPLXRP110 427985 6/18/2007 PATTEEA 427947 7/8/2007 PATTEEA 427944 HELP Please. I am trying to do these myself but I am struggling. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIf embedded in an IF statement
=SUMPRODUCT(--(MONTH('Raw Data'!A2:A14000)=6),--('Raw
Data'!B2:B14000="XIAPPLXRP110")) you can not use whole columns (e.g A:A) unless you use XL2007 "Amber" wrote: Good Morning, I have a situation where I would like to count the number of orders created by a certain person within a specific month. What I would like to say is Count the number of orders that were placed in the month of June for XIAPPLXRP110 Here is the formula I was trying to use which obviously did not work. =IF('Raw Data'!a2:a14000(left,A2,2,=6/),COUNTIF('Raw Data'!B:B,"XIAPPLXRP110"),"") Here is some sample data below. Date Creator Order # 6/5/2007 SCHMINKN 427998 7/24/2007 SCHMINKN 427954 6/27/2007 XIAPPLXRP110 428031 7/2/2007 XIAPPLXRP110 427985 6/18/2007 PATTEEA 427947 7/8/2007 PATTEEA 427944 HELP Please. I am trying to do these myself but I am struggling. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIf embedded in an IF statement
If I may ask, what are the -- for???
"Toppers" wrote: already replied to your previous posting ... be patient! =SUMPRODUCT(--(MONTH(A2:A100)=X1),--(B2:B100=X2)) X1=6 (June) (assume all in same year) X2=XIAPPLXRP110 "Amber" wrote: Good Morning, I have a situation where I would like to count the number of orders created by a certain person within a specific month. What I would like to say is Count the number of orders that were placed in the month of June for XIAPPLXRP110 Here is the formula I was trying to use which obviously did not work. =IF('Raw Data'!a2:a14000(left,A2,2,=6/),COUNTIF('Raw Data'!B:B,"XIAPPLXRP110"),"") Here is some sample data below. Date Creator Order # 6/5/2007 SCHMINKN 427998 7/24/2007 SCHMINKN 427954 6/27/2007 XIAPPLXRP110 428031 7/2/2007 XIAPPLXRP110 427985 6/18/2007 PATTEEA 427947 7/8/2007 PATTEEA 427944 HELP Please. I am trying to do these myself but I am struggling. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIf embedded in an IF statement
the -- convert a TRUE/FALSE to 1/0 so SUMPRODUCT can then do the arithmetic.
an alternative is: =SUMPRODUCT((MONTH('Raw Data'!A2:A14000)=6)*('Raw Data'!:B14000="XIAPPLXRP110")) HTH "Teethless mama" wrote: =SUMPRODUCT(--(MONTH('Raw Data'!A2:A14000)=6),--('Raw Data'!B2:B14000="XIAPPLXRP110")) you can not use whole columns (e.g A:A) unless you use XL2007 "Amber" wrote: Good Morning, I have a situation where I would like to count the number of orders created by a certain person within a specific month. What I would like to say is Count the number of orders that were placed in the month of June for XIAPPLXRP110 Here is the formula I was trying to use which obviously did not work. =IF('Raw Data'!a2:a14000(left,A2,2,=6/),COUNTIF('Raw Data'!B:B,"XIAPPLXRP110"),"") Here is some sample data below. Date Creator Order # 6/5/2007 SCHMINKN 427998 7/24/2007 SCHMINKN 427954 6/27/2007 XIAPPLXRP110 428031 7/2/2007 XIAPPLXRP110 427985 6/18/2007 PATTEEA 427947 7/8/2007 PATTEEA 427944 HELP Please. I am trying to do these myself but I am struggling. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIf embedded in an IF statement
When I tried to use these, I received a #REF!
What does this mean? Whenever I use the sumproduct function, I have trouble. I know this is frustrating for you but it is for me too.. Thanks, "Toppers" wrote: the -- convert a TRUE/FALSE to 1/0 so SUMPRODUCT can then do the arithmetic. an alternative is: =SUMPRODUCT((MONTH('Raw Data'!A2:A14000)=6)*('Raw Data'!:B14000="XIAPPLXRP110")) HTH "Teethless mama" wrote: =SUMPRODUCT(--(MONTH('Raw Data'!A2:A14000)=6),--('Raw Data'!B2:B14000="XIAPPLXRP110")) you can not use whole columns (e.g A:A) unless you use XL2007 "Amber" wrote: Good Morning, I have a situation where I would like to count the number of orders created by a certain person within a specific month. What I would like to say is Count the number of orders that were placed in the month of June for XIAPPLXRP110 Here is the formula I was trying to use which obviously did not work. =IF('Raw Data'!a2:a14000(left,A2,2,=6/),COUNTIF('Raw Data'!B:B,"XIAPPLXRP110"),"") Here is some sample data below. Date Creator Order # 6/5/2007 SCHMINKN 427998 7/24/2007 SCHMINKN 427954 6/27/2007 XIAPPLXRP110 428031 7/2/2007 XIAPPLXRP110 427985 6/18/2007 PATTEEA 427947 7/8/2007 PATTEEA 427944 HELP Please. I am trying to do these myself but I am struggling. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIf embedded in an IF statement
What exactly is your formula?
Is the sheet name correct ... no extra blanks? There was a typo in my last posting if you copied this .... =SUMPRODUCT((MONTH('Raw Data'!A2:A14000)=6)*('Raw Data'!B2:B14000="XIAPPLXRP110")) "Amber" wrote: When I tried to use these, I received a #REF! What does this mean? Whenever I use the sumproduct function, I have trouble. I know this is frustrating for you but it is for me too.. Thanks, "Toppers" wrote: the -- convert a TRUE/FALSE to 1/0 so SUMPRODUCT can then do the arithmetic. an alternative is: =SUMPRODUCT((MONTH('Raw Data'!A2:A14000)=6)*('Raw Data'!:B2:B14000="XIAPPLXRP110")) HTH "Teethless mama" wrote: =SUMPRODUCT(--(MONTH('Raw Data'!A2:A14000)=6),--('Raw Data'!B2:B14000="XIAPPLXRP110")) you can not use whole columns (e.g A:A) unless you use XL2007 "Amber" wrote: Good Morning, I have a situation where I would like to count the number of orders created by a certain person within a specific month. What I would like to say is Count the number of orders that were placed in the month of June for XIAPPLXRP110 Here is the formula I was trying to use which obviously did not work. =IF('Raw Data'!a2:a14000(left,A2,2,=6/),COUNTIF('Raw Data'!B:B,"XIAPPLXRP110"),"") Here is some sample data below. Date Creator Order # 6/5/2007 SCHMINKN 427998 7/24/2007 SCHMINKN 427954 6/27/2007 XIAPPLXRP110 428031 7/2/2007 XIAPPLXRP110 427985 6/18/2007 PATTEEA 427947 7/8/2007 PATTEEA 427944 HELP Please. I am trying to do these myself but I am struggling. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIf embedded in an IF statement
I made the necessary changes and it work. If I would like it to say is not
XIAPPLXRP110 can I just change it to say: =SUMPRODUCT((MONTH('Raw Data'!A2:A14000)=6)*('RawData'!B2:B14000< "XIAPPLXRP110")) "Toppers" wrote: What exactly is your formula? Is the sheet name correct ... no extra blanks? There was a typo in my last posting if you copied this .... =SUMPRODUCT((MONTH('Raw Data'!A2:A14000)=6)*('Raw Data'!B2:B14000="XIAPPLXRP110")) "Amber" wrote: When I tried to use these, I received a #REF! What does this mean? Whenever I use the sumproduct function, I have trouble. I know this is frustrating for you but it is for me too.. Thanks, "Toppers" wrote: the -- convert a TRUE/FALSE to 1/0 so SUMPRODUCT can then do the arithmetic. an alternative is: =SUMPRODUCT((MONTH('Raw Data'!A2:A14000)=6)*('Raw Data'!:B2:B14000="XIAPPLXRP110")) HTH "Teethless mama" wrote: =SUMPRODUCT(--(MONTH('Raw Data'!A2:A14000)=6),--('Raw Data'!B2:B14000="XIAPPLXRP110")) you can not use whole columns (e.g A:A) unless you use XL2007 "Amber" wrote: Good Morning, I have a situation where I would like to count the number of orders created by a certain person within a specific month. What I would like to say is Count the number of orders that were placed in the month of June for XIAPPLXRP110 Here is the formula I was trying to use which obviously did not work. =IF('Raw Data'!a2:a14000(left,A2,2,=6/),COUNTIF('Raw Data'!B:B,"XIAPPLXRP110"),"") Here is some sample data below. Date Creator Order # 6/5/2007 SCHMINKN 427998 7/24/2007 SCHMINKN 427954 6/27/2007 XIAPPLXRP110 428031 7/2/2007 XIAPPLXRP110 427985 6/18/2007 PATTEEA 427947 7/8/2007 PATTEEA 427944 HELP Please. I am trying to do these myself but I am struggling. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIf embedded in an IF statement
Yes.
"Amber" wrote: I made the necessary changes and it work. If I would like it to say is not XIAPPLXRP110 can I just change it to say: =SUMPRODUCT((MONTH('Raw Data'!A2:A14000)=6)*('RawData'!B2:B14000< "XIAPPLXRP110")) "Toppers" wrote: What exactly is your formula? Is the sheet name correct ... no extra blanks? There was a typo in my last posting if you copied this .... =SUMPRODUCT((MONTH('Raw Data'!A2:A14000)=6)*('Raw Data'!B2:B14000="XIAPPLXRP110")) "Amber" wrote: When I tried to use these, I received a #REF! What does this mean? Whenever I use the sumproduct function, I have trouble. I know this is frustrating for you but it is for me too.. Thanks, "Toppers" wrote: the -- convert a TRUE/FALSE to 1/0 so SUMPRODUCT can then do the arithmetic. an alternative is: =SUMPRODUCT((MONTH('Raw Data'!A2:A14000)=6)*('Raw Data'!:B2:B14000="XIAPPLXRP110")) HTH "Teethless mama" wrote: =SUMPRODUCT(--(MONTH('Raw Data'!A2:A14000)=6),--('Raw Data'!B2:B14000="XIAPPLXRP110")) you can not use whole columns (e.g A:A) unless you use XL2007 "Amber" wrote: Good Morning, I have a situation where I would like to count the number of orders created by a certain person within a specific month. What I would like to say is Count the number of orders that were placed in the month of June for XIAPPLXRP110 Here is the formula I was trying to use which obviously did not work. =IF('Raw Data'!a2:a14000(left,A2,2,=6/),COUNTIF('Raw Data'!B:B,"XIAPPLXRP110"),"") Here is some sample data below. Date Creator Order # 6/5/2007 SCHMINKN 427998 7/24/2007 SCHMINKN 427954 6/27/2007 XIAPPLXRP110 428031 7/2/2007 XIAPPLXRP110 427985 6/18/2007 PATTEEA 427947 7/8/2007 PATTEEA 427944 HELP Please. I am trying to do these myself but I am struggling. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a formula that contains a complex embedded IF statement | Excel Worksheet Functions | |||
| Is This Embedded in The COUNTIF() function??? | Excel Worksheet Functions | |||
| Need help with an embedded IF statement | Excel Worksheet Functions | |||
| list embedded in an if statement | Excel Discussion (Misc queries) | |||
| reducing the number of times an IF statement needs to be calculated in embedded IF statements | Excel Discussion (Misc queries) | |||
Linear Mode
