Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count cells in range that restarts at intervals
The -- is a double unary minus which forces the Sumproduct function to
Convert True and False answers to 1's and 0's. More confused now? There is a great explanation of Sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html and further explanation of the double unary minus from Bob Phillips and Harlan Grove at http://tinyurl.com/bv42x Hope this helps Rowan "Melissa" wrote: Fantastic! Worked like a charm... but can you explain what the "--" means? "Rowan" wrote: If you data starts in Row 2 with the Names in column A and the Y/N in column B then in C2 put the formula: =SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2="Y")) and copy down. Hope this helps Rowan "Melissa" wrote: I have a table like this: Row Name Y/N? No. of Y 1 Adam Y 1 2 Adam Y 2 3 Adam N 2 4 Adam N 2 1 Brian Y 1 2 Brian N 1 3 Brian N 1 4 Brian Y 2 Is there a formula I can put for "No. of Y" that counts the no. of times "Y" appears for Adam from the first "Adam" row up to the current row? The counting should restart for Brian and not accumulate from Adam's total. Note that the starting point for each person is when "Row" = 1. Can a pivot table help? |
#2
|
|||
|
|||
If you data starts in Row 2 with the Names in column A and the Y/N in column
B then in C2 put the formula: =SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2="Y")) and copy down. Hope this helps Rowan "Melissa" wrote: I have a table like this: Row Name Y/N? No. of Y 1 Adam Y 1 2 Adam Y 2 3 Adam N 2 4 Adam N 2 1 Brian Y 1 2 Brian N 1 3 Brian N 1 4 Brian Y 2 Is there a formula I can put for "No. of Y" that counts the no. of times "Y" appears for Adam from the first "Adam" row up to the current row? The counting should restart for Brian and not accumulate from Adam's total. Note that the starting point for each person is when "Row" = 1. Can a pivot table help? |
#3
|
|||
|
|||
Fantastic! Worked like a charm...
but can you explain what the "--" means? "Rowan" wrote: If you data starts in Row 2 with the Names in column A and the Y/N in column B then in C2 put the formula: =SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2="Y")) and copy down. Hope this helps Rowan "Melissa" wrote: I have a table like this: Row Name Y/N? No. of Y 1 Adam Y 1 2 Adam Y 2 3 Adam N 2 4 Adam N 2 1 Brian Y 1 2 Brian N 1 3 Brian N 1 4 Brian Y 2 Is there a formula I can put for "No. of Y" that counts the no. of times "Y" appears for Adam from the first "Adam" row up to the current row? The counting should restart for Brian and not accumulate from Adam's total. Note that the starting point for each person is when "Row" = 1. Can a pivot table help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting a range of cells that get value from other cells | Excel Discussion (Misc queries) | |||
adding the same prefix or suffix to a range of cells | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Formula help for using a range of cells! | Excel Worksheet Functions |