IT WORKS - my equation was missing one little comma!!!!
Thanks for all your help. I can achieve what I wanted to.

Thanks again


"Jenny S" wrote:

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!

"Dave Peterson" wrote:

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.

Jenny S wrote:

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" wrote:

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

Jenny S wrote:

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" wrote:

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

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



Jenny S wrote:

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 are not 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

"Liliana" wrote:

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


?B?SmVubnkuUw==?= wrote in
:

Sorry, please see my reply to Dave


"Liliana" wrote:

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


?B?SmVubnkuUw==?= wrote in
:

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

Thanks,
Jenny

"Liliana" wrote:


Sumproduct can do this. Backgound reading

http://www.contextures.com/xlFunctio...tml#SumProduct

http://en.allexperts.com/q/Excel-105...adsheet-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



?B?SmVubnkuUw==?= wrote
in
:

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


--

Dave Peterson