How to obtain a total count of two columns for a specific product
 

Without using two pivot tables, how do I look at two columns and obtain a
count by month/year for a specific product?
Example: My data has Reason and Secondary reason columns and I need to count
by month/year how may times Billing appears in the Reason and secondary
reason column and obtain a total for the month/year.

How to obtain a total count of two columns for a specific product
 

With a data list in rows 1 through 70
whe
Row_1 contains the headings
A2:A70 contains Dates
B2:B70 contains Reason1
C2:C70 contains Reason2
D2:D70 contains Product

and...
F1: (contains the year and month to match
in this format:yyyymm....eg 200802)
G1: (contains the Reason1 to match......eg Billing)
H1: (contains the Reason2 to match......eg Billing)
I1: (contains the Product to match......eg Widget)

Using the example above, this formula returns the count of
Widgets in Feb-2008
whe Reason1 = Billing and Reason = Billing

J1: =SUMPRODUCT((--TEXT(A2:A70,"yyyymm")=F1)*
(B2:B70=G1)*(C2:C70=H1)*(D2:D70=I1))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Jennifer" wrote in message
...
Without using two pivot tables, how do I look at two columns and obtain a
count by month/year for a specific product?
Example: My data has Reason and Secondary reason columns and I need to
count
by month/year how may times Billing appears in the Reason and secondary
reason column and obtain a total for the month/year.

How to obtain a total count of two columns for a specific prod
 

Thank you I tried your formula, set up a dummy worksheet with the values you
indicated below. I am receiving a #VALUE error. Any ideas?
Also will this count even if the two reason do not match in the same row ?
Sometimes we can have two different reasons for one row. Just like to get a
total count of , see example below" Billing" for "Claims".
Should get 2 for Billing IN Claims
Date Reason1 Reason2 Product
200602 Billing Cancel Claims
200602 Cancel Billing Claims
200602 Ads Cancel Claims
200602 Misc Misc Claims
200602 Billing Ads PS

"Ron Coderre" wrote:

With a data list in rows 1 through 70
whe
Row_1 contains the headings
A2:A70 contains Dates
B2:B70 contains Reason1
C2:C70 contains Reason2
D2:D70 contains Product

and...
F1: (contains the year and month to match
in this format:yyyymm....eg 200802)
G1: (contains the Reason1 to match......eg Billing)
H1: (contains the Reason2 to match......eg Billing)
I1: (contains the Product to match......eg Widget)

Using the example above, this formula returns the count of
Widgets in Feb-2008
whe Reason1 = Billing and Reason = Billing

J1: =SUMPRODUCT((--TEXT(A2:A70,"yyyymm")=F1)*
(B2:B70=G1)*(C2:C70=H1)*(D2:D70=I1))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Jennifer" wrote in message
...
Without using two pivot tables, how do I look at two columns and obtain a
count by month/year for a specific product?
Example: My data has Reason and Secondary reason columns and I need to
count
by month/year how may times Billing appears in the Reason and secondary
reason column and obtain a total for the month/year.

How to obtain a total count of two columns for a specific prod
 

OK....Having some data and more details helps quite a bit.

My formula assumed that Col_A contained actual Dates
and that "Billing" needed to be in both Col_B and Col_C

So....Using the same parameter structure I posted:

If Col_A contains dates (instead of YYYYMM)
and "Billing" needs to be in either Col_B or Col_C,
try this:
J1: =SUMPRODUCT((--TEXT(A2:A70,"yyyymm")=F1)*
(((B2:B70=G1)+(C2:C70=H1))0)*(D2:D70=I1))

otherwise, if Col_A does contain YYYYMM values,
then try this:
=SUMPRODUCT((A2:A70=F1)*
(((B2:B70=G1)+(C2:C70=H1))0)*(D2:D70=I1))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Jennifer" wrote in message
...
Thank you I tried your formula, set up a dummy worksheet with the values
you
indicated below. I am receiving a #VALUE error. Any ideas?
Also will this count even if the two reason do not match in the same row ?
Sometimes we can have two different reasons for one row. Just like to get
a
total count of , see example below" Billing" for "Claims".
Should get 2 for Billing IN Claims
Date Reason1 Reason2 Product
200602 Billing Cancel Claims
200602 Cancel Billing Claims
200602 Ads Cancel Claims
200602 Misc Misc Claims
200602 Billing Ads PS

"Ron Coderre" wrote:

With a data list in rows 1 through 70
whe
Row_1 contains the headings
A2:A70 contains Dates
B2:B70 contains Reason1
C2:C70 contains Reason2
D2:D70 contains Product

and...
F1: (contains the year and month to match
in this format:yyyymm....eg 200802)
G1: (contains the Reason1 to match......eg Billing)
H1: (contains the Reason2 to match......eg Billing)
I1: (contains the Product to match......eg Widget)

Using the example above, this formula returns the count of
Widgets in Feb-2008
whe Reason1 = Billing and Reason = Billing

J1: =SUMPRODUCT((--TEXT(A2:A70,"yyyymm")=F1)*
(B2:B70=G1)*(C2:C70=H1)*(D2:D70=I1))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Jennifer" wrote in message
...
Without using two pivot tables, how do I look at two columns and obtain
a
count by month/year for a specific product?
Example: My data has Reason and Secondary reason columns and I need to
count
by month/year how may times Billing appears in the Reason and
secondary
reason column and obtain a total for the month/year.

How to obtain a total count of two columns for a specific prod
 

Thank you Ron, that is so much easier than creating two pivots and pasting
the info into a table. Much appreciated. I knew there had to be a way.

"Ron Coderre" wrote:

OK....Having some data and more details helps quite a bit.

My formula assumed that Col_A contained actual Dates
and that "Billing" needed to be in both Col_B and Col_C

So....Using the same parameter structure I posted:

If Col_A contains dates (instead of YYYYMM)
and "Billing" needs to be in either Col_B or Col_C,
try this:
J1: =SUMPRODUCT((--TEXT(A2:A70,"yyyymm")=F1)*
(((B2:B70=G1)+(C2:C70=H1))0)*(D2:D70=I1))

otherwise, if Col_A does contain YYYYMM values,
then try this:
=SUMPRODUCT((A2:A70=F1)*
(((B2:B70=G1)+(C2:C70=H1))0)*(D2:D70=I1))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Jennifer" wrote in message
...
Thank you I tried your formula, set up a dummy worksheet with the values
you
indicated below. I am receiving a #VALUE error. Any ideas?
Also will this count even if the two reason do not match in the same row ?
Sometimes we can have two different reasons for one row. Just like to get
a
total count of , see example below" Billing" for "Claims".
Should get 2 for Billing IN Claims
Date Reason1 Reason2 Product
200602 Billing Cancel Claims
200602 Cancel Billing Claims
200602 Ads Cancel Claims
200602 Misc Misc Claims
200602 Billing Ads PS

"Ron Coderre" wrote:

With a data list in rows 1 through 70
whe
Row_1 contains the headings
A2:A70 contains Dates
B2:B70 contains Reason1
C2:C70 contains Reason2
D2:D70 contains Product

and...
F1: (contains the year and month to match
in this format:yyyymm....eg 200802)
G1: (contains the Reason1 to match......eg Billing)
H1: (contains the Reason2 to match......eg Billing)
I1: (contains the Product to match......eg Widget)

Using the example above, this formula returns the count of
Widgets in Feb-2008
whe Reason1 = Billing and Reason = Billing

J1: =SUMPRODUCT((--TEXT(A2:A70,"yyyymm")=F1)*
(B2:B70=G1)*(C2:C70=H1)*(D2:D70=I1))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Jennifer" wrote in message
...
Without using two pivot tables, how do I look at two columns and obtain
a
count by month/year for a specific product?
Example: My data has Reason and Secondary reason columns and I need to
count
by month/year how may times Billing appears in the Reason and
secondary
reason column and obtain a total for the month/year.