Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to add entires in column based on another column...
I have two columns in a spreadsheet, and I need to count the entires in
column two depending on column 1...For example: Column 1 Column 2 Mike Yes Mike No Tom Yes Mike Yes Tom No So In the above example I need to add all the Yes's that Mike has, not all the yes's just the ones associated to the row Mike is on... Any ideas, or does this make sense. ....Mike |
#2
|
|||
|
|||
Sumproduct() will work
=sumproduct(--(column1range="Mike"),--(Column2range="Yes")) the --( changes the logical true false to a 1,0 numeric response. the arrays in each section must be the same size but cannot be the shorthand for full rows or columns (not A:A) "msbutton27" wrote: I have two columns in a spreadsheet, and I need to count the entires in column two depending on column 1...For example: Column 1 Column 2 Mike Yes Mike No Tom Yes Mike Yes Tom No So In the above example I need to add all the Yes's that Mike has, not all the yes's just the ones associated to the row Mike is on... Any ideas, or does this make sense. ...Mike |
#3
|
|||
|
|||
This is what I just tried and it fails - did I do something wrong:
=sumproduct(--(RAW Data'!D1:D400="Mike"),--(RAW Data'!K1:K400="yes")) I am typing this into the formula bar in Excel - sorry but I am not that advance with Excel, there afraid I am missing something... THanks for any help, Mike "bj" wrote: Sumproduct() will work =sumproduct(--(column1range="Mike"),--(Column2range="Yes")) the --( changes the logical true false to a 1,0 numeric response. the arrays in each section must be the same size but cannot be the shorthand for full rows or columns (not A:A) "msbutton27" wrote: I have two columns in a spreadsheet, and I need to count the entires in column two depending on column 1...For example: Column 1 Column 2 Mike Yes Mike No Tom Yes Mike Yes Tom No So In the above example I need to add all the Yes's that Mike has, not all the yes's just the ones associated to the row Mike is on... Any ideas, or does this make sense. ...Mike |
#4
|
|||
|
|||
You dropped a couple of single quotes:
=sumproduct(--('RAW Data'!D1:D400="Mike"),--('RAW Data'!K1:K400="yes")) (both before "RAW".) msbutton27 wrote: This is what I just tried and it fails - did I do something wrong: =sumproduct(--(RAW Data'!D1:D400="Mike"),--(RAW Data'!K1:K400="yes")) I am typing this into the formula bar in Excel - sorry but I am not that advance with Excel, there afraid I am missing something... THanks for any help, Mike "bj" wrote: Sumproduct() will work =sumproduct(--(column1range="Mike"),--(Column2range="Yes")) the --( changes the logical true false to a 1,0 numeric response. the arrays in each section must be the same size but cannot be the shorthand for full rows or columns (not A:A) "msbutton27" wrote: I have two columns in a spreadsheet, and I need to count the entires in column two depending on column 1...For example: Column 1 Column 2 Mike Yes Mike No Tom Yes Mike Yes Tom No So In the above example I need to add all the Yes's that Mike has, not all the yes's just the ones associated to the row Mike is on... Any ideas, or does this make sense. ...Mike -- Dave Peterson |
#5
|
|||
|
|||
It works like a charm....
Thanks, Mike "Dave Peterson" wrote: You dropped a couple of single quotes: =sumproduct(--('RAW Data'!D1:D400="Mike"),--('RAW Data'!K1:K400="yes")) (both before "RAW".) msbutton27 wrote: This is what I just tried and it fails - did I do something wrong: =sumproduct(--(RAW Data'!D1:D400="Mike"),--(RAW Data'!K1:K400="yes")) I am typing this into the formula bar in Excel - sorry but I am not that advance with Excel, there afraid I am missing something... THanks for any help, Mike "bj" wrote: Sumproduct() will work =sumproduct(--(column1range="Mike"),--(Column2range="Yes")) the --( changes the logical true false to a 1,0 numeric response. the arrays in each section must be the same size but cannot be the shorthand for full rows or columns (not A:A) "msbutton27" wrote: I have two columns in a spreadsheet, and I need to count the entires in column two depending on column 1...For example: Column 1 Column 2 Mike Yes Mike No Tom Yes Mike Yes Tom No So In the above example I need to add all the Yes's that Mike has, not all the yes's just the ones associated to the row Mike is on... Any ideas, or does this make sense. ...Mike -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format based on data from another column | Excel Discussion (Misc queries) | |||
Program Column B to record numerical range based on number in colm | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Hide column based on data value | Excel Discussion (Misc queries) | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions |