Counting data that meets 3 different Criteria

[Jenny.S]

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]

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 here:
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/assistance/2002/articles/xlconPT101.aspx

 

[Liliana]

Sumproduct can do this. Backgound reading

http://www.contextures.com/xlFunctions04.html#SumProduct

http://en.allexperts.com/q/Excel-1059/Spreadsheet-Count-functions.htm


Example

Count the number of matches in the range A1:C12 that match the criterea in
cells A16-C15



=SUMPRODUCT(--(MONTH($B$1:$B$12)=MONTH(B15))*($C$1:$C$12=C15)*($D$1:$D$12
=D15))


The above assumes:

- B15 contains a full date (i.e not "June" or "6" but for example
"6/6/2009") and $B$1:$B$12 contains dates
- C16 and D16 contain values that match data from the above columns and
require no conversion.



You many find it easier to handle you date matching if you use a helper
column to convert your date to month:

=TEXT(B1,"mmm")
Where B1 is a date

The result becomes Jun

For example, if you converted in column E, your formula then becomes

=SUMPRODUCT(($C$1:$C$12=C15)*($D$1:$D$12=D15)*($E$1:$ED$12=E15))

--
Lil

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.

--

 

[Jenny.S]

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

 

[Jenny.S]

Thanks, please see my reply to Dave. Can you help?

Thanks,
Jenny

 

[Dave Peterson]

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

Share the formula you tried.

 

[Liliana]

Similar to Dave, if you can't work out how to make your formula work,
you need to provide further information.

Thanks, please see my reply to Dave. Can you help?

Thanks,
Jenny

--

 

[Jenny.S]

Sorry about that, I had thought that after I posted and logged off. Below
is the equation, $A$1 is a cell in the second tab that the month is put into
that I want to report. B3:B17 is a list of department, TD is the column that
the department is in in the 1st tab, C2:W2 is the injury types, TI is the
column that the injury is in in the 1st tab. There are 292 records in the
1st tab. Pivot table gives a great summary, but I'm not too happy with the
graph. Need to have a play with that. The result of the formula below is
"0", it should be "2"

=SUMPRODUCT(--(TD=B3)*(TI=C2)*(M=$A$1))

Sorry, I should have posted my equation last time.
Thanks,
Jenny.S

 

[Jenny.S]

Sorry, please see my reply to Dave

Similar to Dave, if you can't work out how to make your formula work,
you need to provide further information.

 

[Bernd P]

Hello,

IMHO Sumproduct is a suboptimal approach here. See here why:
http://sulprobil.com/html/sumproduct.html

If you do not like to use pivot tables I suggest to use one of my
UDF's Pfreq, Mfreq or Pstat:
http://sulprobil.com/html/listfreq.html

Regards,
Bernd

 

[Dave Peterson]

You can't use the whole column unless you're using xl2007.

try:
=SUMPRODUCT(--(TD1:Td999=B3),--(TI1:Ti999=C2),--(m1:m999=$A$1))
or in xl2007
=SUMPRODUCT(--(TD:Td=B3),--(TI:Ti=C2),--(m:m=$A$1))

Sorry about that, I had thought that after I posted and logged off. Below
is the equation, $A$1 is a cell in the second tab that the month is put into
that I want to report. B3:B17 is a list of department, TD is the column that
the department is in in the 1st tab, C2:W2 is the injury types, TI is the
column that the injury is in in the 1st tab. There are 292 records in the
1st tab. Pivot table gives a great summary, but I'm not too happy with the
graph. Need to have a play with that. The result of the formula below is
"0", it should be "2"

=SUMPRODUCT(--(TD=B3)*(TI=C2)*(M=$A$1))

Sorry, I should have posted my equation last time.
Thanks,
Jenny.S

 

[Liliana]

Tested and working (for me)

=SUMPRODUCT((MONTH(M)=MONTH($A$1))*(TD=$B$1)*(TI=$C$1))

You would need to change $B$1 and $C$1 to your relevant cells which
should make no difference.

In cell A1 and range M you need date values (not months).

To change this to allow you to enter (say, 6 for June) instead of a full
in cell A1

=SUMPRODUCT((MONTH(m)=$A$1)*(TD=$B$1)*(TI=$C$1))


NOTE M, TD and TI need to be the same size. For example in my test I
defined

M =Sheet1!$A$2:$A$6
TD =Sheet1!$B$2:$B$6
TI =Sheet1!$C$4:$C$8

All have the same number of rows, If one of the ranges has more or less
cells, the result is $N/A

--
Lil

Sorry, please see my reply to Dave

--

 

[Jenny S]

Sorry, been having trouble with my computer and couldn't reply! I am using
Excel 2003. Below hopefully helps you better with what I am trying to do and
why:

SUMPRODUCT(--(TD=B3),--(TI=C2),--(M=$A$1))
Result is “0â€

=SUMPRODUCT((MONTH(M)=$A$1)*(TD=$B$3)*(TI=$C$2))
Result is “Valueâ€

Take out the word “Month†and change to: (--(M=$A$1)
Result is “0â€

TD, TI & M are all defined names from row 6:500.
M = A6:A500
TD = E6:E500
TI = F6:F500

Tab 1 is the raw data, Tab 2 is my summary, 3 more tabs for graphs from the
summary.

I wanted to be able to let the user input the month into $A$1 on tab 2, and
protect the rest of the worksheet so that the equations arenot lost or
changed. Graphs would then be in tabs 2, 4, & 5 that would automatically
update from the summary tab. This would make it simple for the end user.

Pivot Table does do the work nicely for me, but I think my users may not be
clever enough to do the manual changes required to get graphs etc. KISS !!

Many thanks for your help so far.
Jenny

 

[Jenny S]

See my reply to Liliana, thanks heaps for your help so far. Not quite there!

Jenny

 

[Dave Peterson]

You didn't point at tab 2 in your formula:

...,--(m='tab 2'!$a$1))

 

[Jenny S]

My equations are all in tab 2, looking at $A$1 in tab 2, but other
references TD, TI & M are in tab 1

 

[Dave Peterson]

Are you sure that the values match exactly--maybe there's extra spaces in one
and not the other???

My equations are all in tab 2, looking at $A$1 in tab 2, but other
references TD, TI & M are in tab 1

 

[Jenny S]

Data in "M" is definded as below which return "Jan". In Cell $A$1 is "Jan"
in the same sell format. Any ideas?
=IF(B11,TEXT(B7,"mmm"),0)

 

[Dave Peterson]

I don't see anything wrong with your formula--so it has to be the data.

Set up a test worksheet and try your formula there. Use just enough data to get
a result.

Data in "M" is definded as below which return "Jan". In Cell $A$1 is "Jan"
in the same sell format. Any ideas?
=IF(B11,TEXT(B7,"mmm"),0)

 

[Jenny S]

Thankyou so much, have put some sample data in a new spreadsheet and it
works!!!!! now just need to figure out the main data!
Thanks heaps - at least I know it should work!