I need to add an AND statement to this...

February 18, 2011 at 14:43:02
Specs: Windows XP
*

The AND statement should be included so that I achieve the following: IF '30666'!$P$5:$P$999="FEDEX-Air-Express Saver (3 Day)" AND '30666'!$Q$5:$Q$999<>"Warranty Material Ship Only" then it will sum the frequency.


=SUM(--(FREQUENCY(IF('30666'!$P$5:$P$999="FEDEX-Air-Express Saver (3 Day)",MATCH('30666'!$O$5:$O$999,'30666'!$O$5:$O$999,0)),ROW(INDIRECT("1:"&ROWS('30666'!$O$5:$O$999))))>0))

**


See More: I need to add an AND statement to this...

Report •


#1
February 18, 2011 at 15:08:06
Here's what I'd like to offer:

I have not worked with FREQUENCY very often, but I'm pretty sure I can figure out a solution if you'll do me a favor.

Post a short table of example data, along with the desired results, so that I can fully understand what you are looking for. Obviously I don't 994 rows of data. Maybe 5 - 10 rows so I that can see what your formula does with your actual data and then I can work on a solution.

Please read the How To referenced in my signature line so that the example data you post will line up correctly.

Feel free to simplify things to make it easier to post, e.g. shorter ranges, shorter strings.

=SUM(--(FREQUENCY(IF('30666'!$P$5:$P$9="FEDEX",
MATCH('30666'!$O$5:$O$9,'30666'!$O$5:$O$9,0)),
ROW(INDIRECT("1:"&ROWS('30666'!$O$5:$O$9))))>0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 20, 2011 at 05:48:28
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
10/7/2010	FEDEX-Air-Priority Overnight	Ship Only
10/11/2010	FEDEX-Air-Priority Overnight	Ship Only
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
12/7/2010	FEDEX-Air-Express Saver (3 Day)	Warranty Material Ship Only
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice


The ideal result would be 0 .

If "FEDEX-Air-Expresss Saver (3 Day)" was substituted with the other FEDEX option listed above then the ideal result would be 2


Report •

#3
February 20, 2011 at 05:56:04
Hopefully that's enough information... Thanks

Report •

Related Solutions

#4
February 20, 2011 at 08:28:01
Thanks for the example, but I'm not quite that is enough.

Keep in mind that I can't see your spreadsheet from where I'm sitting, so I have to make some assumptions.

Since you didn't include column headings, I'll assume from your original post that the "Carrier" data is in Column P and the "Shipment Type" is in Column Q.

However, I don't what you are doing with the MATCH function in Column O, so I can't include that in any solution.

That said, I can get the results you asked for (0 or 2) from your example data with this formula:

=SUMPRODUCT(($P$1:$P$8="FEDEX-Air-Express Saver (3 Day)") * ($Q$1:$Q$8<>"Warranty Material Ship Only"))

I don't know if that is enough for you, since I don't know what you are doing with the MATCH function and Column O.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
February 21, 2011 at 07:27:21
I don't think that's going to work. Let me try and explain this a little more clearly...

I have to monitor the number of (unique) express shipments in an order that are not a Warranty Material Ship Only line type (Column Q). In this example I have 3 of them, one on 10/7, one on 10/11, and one on 12/7. The Match function is counting the number of unique ship dates (Column O).

I have formulas above for each of the express shipping methods and they do indeed count the number of (unique) express shipments but I don't want them to include line types that are Warranty Material Ship Only.

Here is the data:

O	P	Q
Ship Date	Shipping Method	Line Type
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
10/4/2010	COMMON-Parcel-Ground	Ship and Invoice
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
	COMMON-Parcel-Ground	Wait To Fulfill
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
10/7/2010	FEDEX-Air-Priority Overnight	Ship Only
10/11/2010	FEDEX-Air-Priority Overnight	Ship Only
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice
12/7/2010	FEDEX-Air-Express Saver (3 Day)	Warranty Material Ship Only
9/28/2010	COMMON-Parcel-Ground	Ship and Invoice


Report •

#6
February 21, 2011 at 08:18:35
Why do you say that you have 3 Express shipments in your example?

Are the 2 FEDEX-Air-Priority Overnight entries considered "express"?

Your OP doesn't seem to indicate that.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
February 21, 2011 at 08:45:32
Note the columns didn't quite line up... Column O = Ship Date, Column P = Shipping Method, and Column Q = Line Type.

There are 3 separate express shipments in my example, the rest shipped common ground:

- One express shipment on 10/7/2010 shipped FEDEX-Air-Priority Overnight
- One express shipment on 10/11/2010 shipped FEDEX-Air-Priority Overnight
- One express shipment on 12/7/2010 shipped FEDEX-Air-Express Saver (3 Day)

I need to count the total number of "unique" express shipments per order that did not ship as Warranty Material Ship Only line type (2 in my example), not the quantity of lines that shipped express (3 in my example). Look at the dates the material shipped. Several lines could ship on the same day -- but that would be considered one shipment.

In an effort not to confuse things let's replace my original formula above with "FEDEX-Air-Priority Overnight" and replace the shipment on 12/7/2010 with "FEDEX-Air-Priority Overnight" but leave the line type as Warranty Material Ship Only. The result from my original formula would total 3 separate shipments but I don't want to include the warranty material ship only lines, so the formula needs to be modified to not include them. My apologies if I have confused you!


Report •

#8
February 21, 2011 at 14:48:44
What if we attack this from a different direction?

Will a "Warranty Material Ship Only" ever ship Common Ground?

If not, then how about this...I'm just taking a shot here:

If your formula is already working for determining the number of unique Express shipments, why not just subtract the number of "Warranty Material Ship Only" found in Column P? Wouldn't that leave you with the number of Express shipments that weren't a Warranty shipment?

If I sent you an email address via PM could you send me a copy of the workbook (with any confidential data removed) so that I can see you original formula working in real life? It's often difficult to set up a worksheet exactly as a required without actually seeing it, especially when we're using complex formulas such as yours. It doesn't take much to get something just a little bit wrong and then we're chasing issues that shouldn't even be involved.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
February 22, 2011 at 07:23:20
Sure, you can send me an email address and I'll forward on to you the workbook.

Report •

Ask Question