| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I count the times a number appears in a table using criteri
I'm getting really confused....
I need to count how many times a number appears if it has a specific word written next to it....for example.... A B 1 1 Summer 1 2 2 Summer 2 3 1 Autumn 1 4 3 Autumn 2 I need to count how many times the number 1, 2 and 3 appear next to Autumn 2 in the table..... does that make any sense? I've tried using the Dcount function and either I'm not doing it right, or it doesn't work for this... The countif formula works if I don't need to specify summer or autumn, but in this case I do... Can anyone help?...Please? It's driving me nuts! Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I count the times a number appears in a table using criteri
Hi,
=SUMPRODUCT((A1:A22=ROW(A1))*(B1:B22="Summer 1")) Put this in a cell and drag down 2 rows Row 1 = 1 and summer 1 Row 2 = 2 and summer 1 etc Mike "Scoob_a" wrote: I'm getting really confused.... I need to count how many times a number appears if it has a specific word written next to it....for example.... A B 1 1 Summer 1 2 2 Summer 2 3 1 Autumn 1 4 3 Autumn 2 I need to count how many times the number 1, 2 and 3 appear next to Autumn 2 in the table..... does that make any sense? I've tried using the Dcount function and either I'm not doing it right, or it doesn't work for this... The countif formula works if I don't need to specify summer or autumn, but in this case I do... Can anyone help?...Please? It's driving me nuts! Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I count the times a number appears in a table using criteri
Separately?
=SUMPRODUCT(--(A1:A10=1),--(B1:B10="Autumn 2")) etc. Or all at once? =SUMPRODUCT((A1:A10={1,2,3})*(B1:B10="Autumn 2")) -- __________________________________ HTH Bob "Scoob_a" wrote in message ... I'm getting really confused.... I need to count how many times a number appears if it has a specific word written next to it....for example.... A B 1 1 Summer 1 2 2 Summer 2 3 1 Autumn 1 4 3 Autumn 2 I need to count how many times the number 1, 2 and 3 appear next to Autumn 2 in the table..... does that make any sense? I've tried using the Dcount function and either I'm not doing it right, or it doesn't work for this... The countif formula works if I don't need to specify summer or autumn, but in this case I do... Can anyone help?...Please? It's driving me nuts! Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I count the times a number appears in a table using cri
Brilliant! My brain is happy again!
"Bob Phillips" wrote: Separately? =SUMPRODUCT(--(A1:A10=1),--(B1:B10="Autumn 2")) etc. Or all at once? =SUMPRODUCT((A1:A10={1,2,3})*(B1:B10="Autumn 2")) -- __________________________________ HTH Bob "Scoob_a" wrote in message ... I'm getting really confused.... I need to count how many times a number appears if it has a specific word written next to it....for example.... A B 1 1 Summer 1 2 2 Summer 2 3 1 Autumn 1 4 3 Autumn 2 I need to count how many times the number 1, 2 and 3 appear next to Autumn 2 in the table..... does that make any sense? I've tried using the Dcount function and either I'm not doing it right, or it doesn't work for this... The countif formula works if I don't need to specify summer or autumn, but in this case I do... Can anyone help?...Please? It's driving me nuts! Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I count the times a number appears in a table usingcriteri
Try this:
=SUMPRODUCT((A1:A4=1)*(B1:B4="Autumn2)) Adjust the ranges to suit, but you can't use full-column references (unless you are using XL2007). Change the =1 in the first term to get counts for the other values in column A, or you could use a cell to hold the values. Hope this helps. Pete On Jun 19, 12:59*pm, Scoob_a wrote: I'm getting really confused.... I need to count how many times a number appears if it has a specific word written next to it....for example.... * * *A * * * * B 1 * *1 * * *Summer 1 2 * *2 * * *Summer 2 3 * *1 * * *Autumn 1 4 * *3 * * *Autumn 2 I need to count how many times the number 1, 2 and 3 appear next to Autumn 2 in the table..... does that make any sense? I've tried using the Dcount function and either I'm not doing it right, or it doesn't work for this... The countif formula works if I don't need to specify summer or autumn, but in this case I do... Can anyone help?...Please? *It's driving me nuts! Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count number of times text appears | Excel Discussion (Misc queries) | |||
| How do I count the number of times a value appears? | Excel Worksheet Functions | |||
| display and count the number of times a value appears | Excel Worksheet Functions | |||
| Count Number of Times Something appears | Excel Worksheet Functions | |||
| count the number of times data appears on the same row from two c. | Excel Worksheet Functions | |||
Hybrid Mode
