software

Hi,

I need a little help. Below is a report that I dump in Excel. You will
note that both classifications have an ‘other’ option. How do I write a
formula that will skip classification if I want the total number of File
Breakdown ‘other’ and vise versa.

I have been using a vlookup.

ThanksClassification Breakdown
Product Information 89
Issues 69
Return Information 38
Other 30
Order Status 24
Shipping Information 9
Website 2
Payment/Billing 2
File Breakdown
Deleted 220
Vendors 4
Mailing Responses 3
Other 1
Spam 1
Wholesale 1
Assume your example data is in columns A and B exactly as posted, so
you have a heading in A1 and an upper table in A2 to B9, then another
heading in A10 and lower table in A11 to B16. You can define these as
named ranges - Upper = $A$2:$B$9 and Lower = $A$11:$B$16.

Elsewhere on your sheet you use a cell to specify the category you are
interested in (say, cell E1), another cell to specify whether Upper or
Lower (F1 - quot;Uquot; or quot;Lquot; specifies), then in cell G1 enter this formula:

=IF(F1=quot;Uquot;, VLOOKUP(E1,Upper,2,0),IF(F1=quot;Lquot;,VLOOKUP(E1,Lower,2 ,0),quot;quot;)

So, if you enter quot;Otherquot; in E1 and quot;Lquot; in F1, you will get 1 in G1.
Change F1 to quot;Uquot; and you get 30. If F1 contains anything other than U
or L then G1 displays blank. If the entry in E1 does not match you will
get #N/A.

Hope this helps.

PetePete_UK wrote...
....
gt;Elsewhere on your sheet you use a cell to specify the category you are
gt;interested in (say, cell E1), another cell to specify whether Upper or
gt;Lower (F1 - quot;Uquot; or quot;Lquot; specifies), then in cell G1 enter this formula:
gt;
gt;=IF(F1=quot;Uquot;, VLOOKUP(E1,Upper,2,0),IF(F1=quot;Lquot;,VLOOKUP(E1,Lower,2 ,0),quot;quot;)
....

Your formula's missing a right parenthesis at the end.

This could be simplified to

=VLOOKUP(E1,IF(F1=quot;Uquot;,Upper,IF(F1=quot;Lquot;,Lower,LEFT(E 1,{1024,0}))),2,0)Thanks for the correction, Harlan.

JR, the formula should be:

=IF(F1=quot;Uquot;, VLOOKUP(E1,Upper,2,0),IF(F1=quot;Lquot;,VLOOKUP(E1,Lower,2 ,0),quot;quot;))

PeteThey are not two columns but only one. I broke it out for clarity, my bad.
Try this:

Classification Breakdown
Product Information 89
Issues 69
Return Information 38
Other 30
Order Status 24
Shipping Information 9
Website 2
Payment/Billing 2
File Breakdown
Deleted 220
Vendors 4
Mailing Responses 3
Other 1
Spam 1
Wholesale 1quot;Pete_UKquot; wrote:

gt; Assume your example data is in columns A and B exactly as posted, so
gt; you have a heading in A1 and an upper table in A2 to B9, then another
gt; heading in A10 and lower table in A11 to B16. You can define these as
gt; named ranges - Upper = $A$2:$B$9 and Lower = $A$11:$B$16.
gt;
gt; Elsewhere on your sheet you use a cell to specify the category you are
gt; interested in (say, cell E1), another cell to specify whether Upper or
gt; Lower (F1 - quot;Uquot; or quot;Lquot; specifies), then in cell G1 enter this formula:
gt;
gt; =IF(F1=quot;Uquot;, VLOOKUP(E1,Upper,2,0),IF(F1=quot;Lquot;,VLOOKUP(E1,Lower,2 ,0),quot;quot;)
gt;
gt; So, if you enter quot;Otherquot; in E1 and quot;Lquot; in F1, you will get 1 in G1.
gt; Change F1 to quot;Uquot; and you get 30. If F1 contains anything other than U
gt; or L then G1 displays blank. If the entry in E1 does not match you will
gt; get #N/A.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt;

I posted more info

quot;Harlan Grovequot; wrote:

gt; Pete_UK wrote...
gt; ....
gt; gt;Elsewhere on your sheet you use a cell to specify the category you are
gt; gt;interested in (say, cell E1), another cell to specify whether Upper or
gt; gt;Lower (F1 - quot;Uquot; or quot;Lquot; specifies), then in cell G1 enter this formula:
gt; gt;
gt; gt;=IF(F1=quot;Uquot;, VLOOKUP(E1,Upper,2,0),IF(F1=quot;Lquot;,VLOOKUP(E1,Lower,2 ,0),quot;quot;)
gt; ....
gt;
gt; Your formula's missing a right parenthesis at the end.
gt;
gt; This could be simplified to
gt;
gt; =VLOOKUP(E1,IF(F1=quot;Uquot;,Upper,IF(F1=quot;Lquot;,Lower,LEFT(E 1,{1024,0}))),2,0)
gt;
gt;

Also, the exact cell position of the 'other will change' daily depending on
which dispositions are used.

quot;Pete_UKquot; wrote:

gt; Assume your example data is in columns A and B exactly as posted, so
gt; you have a heading in A1 and an upper table in A2 to B9, then another
gt; heading in A10 and lower table in A11 to B16. You can define these as
gt; named ranges - Upper = $A$2:$B$9 and Lower = $A$11:$B$16.
gt;
gt; Elsewhere on your sheet you use a cell to specify the category you are
gt; interested in (say, cell E1), another cell to specify whether Upper or
gt; Lower (F1 - quot;Uquot; or quot;Lquot; specifies), then in cell G1 enter this formula:
gt;
gt; =IF(F1=quot;Uquot;, VLOOKUP(E1,Upper,2,0),IF(F1=quot;Lquot;,VLOOKUP(E1,Lower,2 ,0),quot;quot;)
gt;
gt; So, if you enter quot;Otherquot; in E1 and quot;Lquot; in F1, you will get 1 in G1.
gt; Change F1 to quot;Uquot; and you get 30. If F1 contains anything other than U
gt; or L then G1 displays blank. If the entry in E1 does not match you will
gt; get #N/A.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt;

I suggest that you do break it into two columns, then. You can do this
using Data | Text-to -Columns, using a single space as delimiter. It
doesn't matter where the quot;Otherquot; rows are located within the tables,
but you must ensure that the named ranges Upper and Lower are adjusted
if the data occupies different areas.

Hope this helps again.

Pete