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

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

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

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

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

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