COUNTIF for summary Table

[Jeremy]

I am attempting to create a summary sheet for a rather large tracking
worksheet. It track individual complaints and their cost to the company.
There are two sheets that I am using Data (where information is entered) and
Monthly Summary (speaks for itself)

The date of the complaints is logged in the Data sheet. A formula
=IF(MONTH(N7)<10,YEAR(N7)&" "&MONTH(N7),YEAR(N7)&" "&MONTH(N7)) breaks down
the month and year for the complaint.

On the summary tab I have a cell that requires the input of that month and
date combiniation. All of the data that I want counted or added up needs to
be based on the data relevant to the month.

**This means that when I type in the date and month in the summary tab, all
of the subsequent data will change.

I am looking to count certain occurances (# of complaints in 2008 1 with the
reason category Non Sales Admin Pricing)

And

I am looking to add the dollar value of certain occurances ($ value of all
occurances in 2008 1 with the reason category Non Sales Admin Pricing)

What I would really like is instead of using the ="Non Sales Admin Pricing"
is reference it to a cell so I can autofill certain other cells with the same
needs.

I have tried to communicate this as well as I can. If you would like to see
a copy of the sheet I am more than willing to emaiol it to you.

Thanks
Jeremy

 

[Sean Timmons]

OK, so, let's say you have your year/month in column P and your issue type in
column B...

Then, in your Summary, say your input cell for month/date is B2 and Your
entry for issue type is C2.

Then:

=sumproduct(--('Data'!P210000=B2),--('Data'!B:B10000=C2))

Gives count of the type you entered during the month entered.

Let's say the costs are in coolumn C, then:

=sumproduct(--('Data'!P210000=B2),--('Data'!B2:B10000=C2),--('Data'!C2:C10000)

Hope that makes sense!!

Will provide that value.

 

[Dave Peterson]

You may be able to use a similar formula in the summary worksheet that spaces
that string the way you want.

Then you could use something like this to get the count:
=countif(data!a:a,'monthly summary'!x9)

And
=sumif(data!a:a,'monthly summary'!x9,data!b:B)
to sum column B of the data worksheet when the strings match.

If you have multiple criteria, you could use use =countifs or =sumifs() in
xl2007.

Or something like this in any version to get a count:

=sumproduct(--(data!a1:a100='monthly summary'!x9),
--(data'!b1:b100=z9))

Where z9 held the category.

=sumproduct(--(data!a1:a100='monthly summary'!x9),
--(data!b1:b100=z9),
(data!c1:c100))

to sum the stuff in column c when both the other columns match.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Jeremy]

Is there an add in that will allow for countifs in xl2003?

 

[Dave Peterson]

Not that I know.

But I bet the worksheet functions would be faster.