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.

Thanks


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 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 - "U" or "L" specifies), then in cell G1 enter this formula:

=IF(F1="U", VLOOKUP(E1,Upper,2,0),IF(F1="L",VLOOKUP(E1,Lower,2 ,0),"")

So, if you enter "Other" in E1 and "L" in F1, you will get 1 in G1.
Change F1 to "U" 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.

Pete

Pete_UK wrote...
....
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 - "U" or "L" specifies), then in cell G1 enter this formula:

=IF(F1="U", VLOOKUP(E1,Upper,2,0),IF(F1="L",VLOOKUP(E1,Lower,2 ,0),"")
....

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

This could be simplified to

=VLOOKUP(E1,IF(F1="U",Upper,IF(F1="L",Lower,LEFT(E 1,{1024,0}))),2,0)

Thanks for the correction, Harlan.

JR, the formula should be:

=IF(F1="U", VLOOKUP(E1,Upper,2,0),IF(F1="L",VLOOKUP(E1,Lower,2 ,0),""))

Pete

They 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 1


"Pete_UK" wrote:

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 - "U" or "L" specifies), then in cell G1 enter this formula:

=IF(F1="U", VLOOKUP(E1,Upper,2,0),IF(F1="L",VLOOKUP(E1,Lower,2 ,0),"")

So, if you enter "Other" in E1 and "L" in F1, you will get 1 in G1.
Change F1 to "U" 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.

Pete

I posted more info

"Harlan Grove" wrote:

Pete_UK wrote...
....
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 - "U" or "L" specifies), then in cell G1 enter this formula:

=IF(F1="U", VLOOKUP(E1,Upper,2,0),IF(F1="L",VLOOKUP(E1,Lower,2 ,0),"")
....

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

This could be simplified to

=VLOOKUP(E1,IF(F1="U",Upper,IF(F1="L",Lower,LEFT(E 1,{1024,0}))),2,0)

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

"Pete_UK" wrote:

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 - "U" or "L" specifies), then in cell G1 enter this formula:

=IF(F1="U", VLOOKUP(E1,Upper,2,0),IF(F1="L",VLOOKUP(E1,Lower,2 ,0),"")

So, if you enter "Other" in E1 and "L" in F1, you will get 1 in G1.
Change F1 to "U" 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.

Pete

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