Share the addresses of the ranges that contain each of the categories.

Share the formula you tried.

Jenny.S wrote:

Thanks, have had a play with pivot tables.
I have named ranges in my spreadsheet, i.e: "M" is the column representing
the month, "TD" is department and "TI" is injury type. I am having trouble
with getting the =sumproduct to work and to put the results in a different
tab.

Do you have any suggestions?

Thanks,
Jenny.S

"Dave Peterson" wrote:

Maybe...

=sumproduct(--(sheet1!a1:a100="accident type 1"),
--(text(sheet1!b1:b100,"yyyymm")="200907"),
--(sheet1!c1:c100="dept number here"))

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

============
But you may want to look into a pivottable. If your data is laid out nicely
(and it sounds like it is), you could get some very nice summary reports pretty
quickly.

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

Jenny.S wrote:

Hi,

Would love some help with this challenge.

Accident data is being recoded in a spreadsheet and has info recorded for 12
month. In a separate tab I want to count the number of instances by a given
month, then by a given department, and then by accident type. Have used
three separate "countif" statements and can get the number of accidents for a
month, the total number for a give department for 12 months, and the total
number by accident type for 12 months. I want to be able to get the total
for a month, by department, then accident type.

Hope someone can help me, thanks.

--

Dave Peterson


--

Dave Peterson