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

[Jennifer]

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.

 

[Ron Coderre]

With a data list in rows 1 through 70
where:
Row_1 contains the headings
A2:A70 contains Dates
B2:B70 contains Reason1
C2:C70 contains Reason2
D270 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
where: Reason1 = Billing and Reason = Billing

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

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

Regards,

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

 

[Jennifer]

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]

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)*(D270=I1))

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

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

Regards,

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

 

[Jennifer]

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.