Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identify the row with specific criteria
Hi
I have a database where column A - D contain country, product, etc, column E - Q contain Jan - Dec and full year volume while column E contain Jan - Dec and full year sales value. Product has sales in particular month should also has sales volume in the same month in principle. Since volume and sales are derived from different sources, some products in particular months may contain volume but no sales value. I established conditional format, e.g. =and(e2<0, S=0) in the sales columns to identify those cells, however, I have to review all (12) columns to identify those products. How can I put a formula (in cell or conditional format) to identify those products which contain whatever month has volume but no sales in the same month. Besides, I often use "sumproduct" (or "sum(if" ) to sum up in multi-conditional circumstances, what is the purpose to put -- in sumproduct formula, could you illustrate by an example. Thank you. Billy L |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identify the row with specific criteria
Maybe use something like this in a helper column........
=if(COUNTA(E2:Q2)=COUNTA(R2:AC2),"OK","INCOMPLETE" ) Change the ranges to fit as needed....... Vaya con Dios, Chuck, CABGx3 "Billy L" wrote: Hi I have a database where column A - D contain country, product, etc, column E - Q contain Jan - Dec and full year volume while column E contain Jan - Dec and full year sales value. Product has sales in particular month should also has sales volume in the same month in principle. Since volume and sales are derived from different sources, some products in particular months may contain volume but no sales value. I established conditional format, e.g. =and(e2<0, S=0) in the sales columns to identify those cells, however, I have to review all (12) columns to identify those products. How can I put a formula (in cell or conditional format) to identify those products which contain whatever month has volume but no sales in the same month. Besides, I often use "sumproduct" (or "sum(if" ) to sum up in multi-conditional circumstances, what is the purpose to put -- in sumproduct formula, could you illustrate by an example. Thank you. Billy L |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identify the row with specific criteria
Hi, Chuck,
it doesn't work... maybe I didn't specify my question clearly. Please see the example below A B C D E F 1 Jan Vol Feb Vol Mar Vol Jan sale Feb sales Mar Sales 2 product W 10 10 10 100 100 3 product X 10 100 4 product Y 10 10 10 100 5 product Z 10 10 100 I like to have a formula in column G to highlight product W (missing Jan sales), Y (missing Jan & Mar sales) and Z (missing Feb sales). Thanks a lot. Billy L "CLR" wrote: Maybe use something like this in a helper column........ =if(COUNTA(E2:Q2)=COUNTA(R2:AC2),"OK","INCOMPLETE" ) Change the ranges to fit as needed....... Vaya con Dios, Chuck, CABGx3 "Billy L" wrote: Hi I have a database where column A - D contain country, product, etc, column E - Q contain Jan - Dec and full year volume while column E contain Jan - Dec and full year sales value. Product has sales in particular month should also has sales volume in the same month in principle. Since volume and sales are derived from different sources, some products in particular months may contain volume but no sales value. I established conditional format, e.g. =and(e2<0, S=0) in the sales columns to identify those cells, however, I have to review all (12) columns to identify those products. How can I put a formula (in cell or conditional format) to identify those products which contain whatever month has volume but no sales in the same month. Besides, I often use "sumproduct" (or "sum(if" ) to sum up in multi-conditional circumstances, what is the purpose to put -- in sumproduct formula, could you illustrate by an example. Thank you. Billy L |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identify the row with specific criteria
The formula I gave you, when entered in a helper column and copied down,
will count the number of values in columns E through Q of each row, (your volume figures) and compare them to a similar count of the values in columns R through AC of the same row (your sales figures). If the counts are the same, the formula will return the text "OK", if they are different, (inidcating that there is a volumn without a sales figure, or vice versa) the formula will return "INCOMPLETE". I had to assume the column designations of R through AC for your sales figures becuause your original post was not clear to me on that matter.....that is why I added my statement that you should adjust the formula as to your ranges. This is to my best understanding of what you are after. Sorry if it does not work for you. Vaya con Dios, Chuck, CABGx3 "Billy L" wrote: Hi, Chuck, it doesn't work... maybe I didn't specify my question clearly. Please see the example below A B C D E F 1 Jan Vol Feb Vol Mar Vol Jan sale Feb sales Mar Sales 2 product W 10 10 10 100 100 3 product X 10 100 4 product Y 10 10 10 100 5 product Z 10 10 100 I like to have a formula in column G to highlight product W (missing Jan sales), Y (missing Jan & Mar sales) and Z (missing Feb sales). Thanks a lot. Billy L "CLR" wrote: Maybe use something like this in a helper column........ =if(COUNTA(E2:Q2)=COUNTA(R2:AC2),"OK","INCOMPLETE" ) Change the ranges to fit as needed....... Vaya con Dios, Chuck, CABGx3 "Billy L" wrote: Hi I have a database where column A - D contain country, product, etc, column E - Q contain Jan - Dec and full year volume while column E contain Jan - Dec and full year sales value. Product has sales in particular month should also has sales volume in the same month in principle. Since volume and sales are derived from different sources, some products in particular months may contain volume but no sales value. I established conditional format, e.g. =and(e2<0, S=0) in the sales columns to identify those cells, however, I have to review all (12) columns to identify those products. How can I put a formula (in cell or conditional format) to identify those products which contain whatever month has volume but no sales in the same month. Besides, I often use "sumproduct" (or "sum(if" ) to sum up in multi-conditional circumstances, what is the purpose to put -- in sumproduct formula, could you illustrate by an example. Thank you. Billy L |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identify the row with specific criteria
Hi, Chuck,
It works when I change your formula to "countif....<0" because the missing sales value showing as zero. thank you. Billy L "CLR" wrote: The formula I gave you, when entered in a helper column and copied down, will count the number of values in columns E through Q of each row, (your volume figures) and compare them to a similar count of the values in columns R through AC of the same row (your sales figures). If the counts are the same, the formula will return the text "OK", if they are different, (inidcating that there is a volumn without a sales figure, or vice versa) the formula will return "INCOMPLETE". I had to assume the column designations of R through AC for your sales figures becuause your original post was not clear to me on that matter.....that is why I added my statement that you should adjust the formula as to your ranges. This is to my best understanding of what you are after. Sorry if it does not work for you. Vaya con Dios, Chuck, CABGx3 "Billy L" wrote: Hi, Chuck, it doesn't work... maybe I didn't specify my question clearly. Please see the example below A B C D E F 1 Jan Vol Feb Vol Mar Vol Jan sale Feb sales Mar Sales 2 product W 10 10 10 100 100 3 product X 10 100 4 product Y 10 10 10 100 5 product Z 10 10 100 I like to have a formula in column G to highlight product W (missing Jan sales), Y (missing Jan & Mar sales) and Z (missing Feb sales). Thanks a lot. Billy L "CLR" wrote: Maybe use something like this in a helper column........ =if(COUNTA(E2:Q2)=COUNTA(R2:AC2),"OK","INCOMPLETE" ) Change the ranges to fit as needed....... Vaya con Dios, Chuck, CABGx3 "Billy L" wrote: Hi I have a database where column A - D contain country, product, etc, column E - Q contain Jan - Dec and full year volume while column E contain Jan - Dec and full year sales value. Product has sales in particular month should also has sales volume in the same month in principle. Since volume and sales are derived from different sources, some products in particular months may contain volume but no sales value. I established conditional format, e.g. =and(e2<0, S=0) in the sales columns to identify those cells, however, I have to review all (12) columns to identify those products. How can I put a formula (in cell or conditional format) to identify those products which contain whatever month has volume but no sales in the same month. Besides, I often use "sumproduct" (or "sum(if" ) to sum up in multi-conditional circumstances, what is the purpose to put -- in sumproduct formula, could you illustrate by an example. Thank you. Billy L |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identify the row with specific criteria
Very good Billy........glad you got it sorted...........thanks for the
feedback. Vaya con Dios, Chuck, CABGx3 "Billy L" wrote in message ... Hi, Chuck, It works when I change your formula to "countif....<0" because the missing sales value showing as zero. thank you. Billy L "CLR" wrote: The formula I gave you, when entered in a helper column and copied down, will count the number of values in columns E through Q of each row, (your volume figures) and compare them to a similar count of the values in columns R through AC of the same row (your sales figures). If the counts are the same, the formula will return the text "OK", if they are different, (inidcating that there is a volumn without a sales figure, or vice versa) the formula will return "INCOMPLETE". I had to assume the column designations of R through AC for your sales figures becuause your original post was not clear to me on that matter.....that is why I added my statement that you should adjust the formula as to your ranges. This is to my best understanding of what you are after. Sorry if it does not work for you. Vaya con Dios, Chuck, CABGx3 "Billy L" wrote: Hi, Chuck, it doesn't work... maybe I didn't specify my question clearly. Please see the example below A B C D E F 1 Jan Vol Feb Vol Mar Vol Jan sale Feb sales Mar Sales 2 product W 10 10 10 100 100 3 product X 10 100 4 product Y 10 10 10 100 5 product Z 10 10 100 I like to have a formula in column G to highlight product W (missing Jan sales), Y (missing Jan & Mar sales) and Z (missing Feb sales). Thanks a lot. Billy L "CLR" wrote: Maybe use something like this in a helper column........ =if(COUNTA(E2:Q2)=COUNTA(R2:AC2),"OK","INCOMPLETE" ) Change the ranges to fit as needed....... Vaya con Dios, Chuck, CABGx3 "Billy L" wrote: Hi I have a database where column A - D contain country, product, etc, column E - Q contain Jan - Dec and full year volume while column E contain Jan - Dec and full year sales value. Product has sales in particular month should also has sales volume in the same month in principle. Since volume and sales are derived from different sources, some products in particular months may contain volume but no sales value. I established conditional format, e.g. =and(e2<0, S=0) in the sales columns to identify those cells, however, I have to review all (12) columns to identify those products. How can I put a formula (in cell or conditional format) to identify those products which contain whatever month has volume but no sales in the same month. Besides, I often use "sumproduct" (or "sum(if" ) to sum up in multi-conditional circumstances, what is the purpose to put -- in sumproduct formula, could you illustrate by an example. Thank you. Billy L |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identify address of cell containing specific data | Excel Discussion (Misc queries) | |||
formula to identify specific cells | Excel Discussion (Misc queries) | |||
search column of text cellto identify those cells with specific w | Excel Worksheet Functions | |||
How do I identify Filter criteria or variable graph title? | Excel Discussion (Misc queries) | |||
Identify missing criteria | Excel Worksheet Functions |