CountIf embedded in an IF statement

[Guest]

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.

 

[Guest]

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

 

[Guest]

=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

 

[Guest]

If I may ask, what are the -- for???

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

 

[Guest]

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

 

[Guest]

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,

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Yes.

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