|
Counting data that meets 3 different Criteria
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. |
Counting data that meets 3 different Criteria
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 |
Counting data that meets 3 different Criteria
Sumproduct can do this. Backgound reading http://www.contextures.com/xlFunctio...tml#SumProduct http://en.allexperts.com/q/Excel-105...-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. -- |
Counting data that meets 3 different Criteria
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 |
Counting data that meets 3 different Criteria
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...-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. -- |
Counting data that meets 3 different Criteria
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 |
Counting data that meets 3 different Criteria
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...-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. -- -- |
Counting data that meets 3 different Criteria
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 "Dave Peterson" wrote: 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 |
Counting data that meets 3 different Criteria
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...-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. -- -- |
Counting data that meets 3 different Criteria
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 |
Counting data that meets 3 different Criteria
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)) Jenny.S wrote: 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
Counting data that meets 3 different Criteria
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. -- -- -- |
Counting data that meets 3 different Criteria
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 "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. -- -- -- |
Counting data that meets 3 different Criteria
See my reply to Liliana, thanks heaps for your help so far. Not quite there!
Jenny "Dave Peterson" wrote: 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)) Jenny.S wrote: 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
Counting data that meets 3 different Criteria
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 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 "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 |
Counting data that meets 3 different Criteria
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 |
Counting data that meets 3 different Criteria
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 |
Counting data that meets 3 different Criteria
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 |
Counting data that meets 3 different Criteria
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 |
Counting data that meets 3 different Criteria
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 |
Counting data that meets 3 different Criteria
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 |
| All times are GMT +1. The time now is 09:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com