Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting a mixed text/number column based on text in another colum
I just can't get the syntax right! I am looking for a formula for analyzing
read: summing a spreadsheet function based on the contents in another column. In the example that follows, I need to count Apple, Orange or Pear based on whether Apple, Orange or Pear is in V4 or V5 listed in another column. Column Row E F G 1 SS-Apple-0145 V4 2 SS-Orange-0134 V5 3 SS-Pear-0123 V4 4 SS-Apple-0146 V5 5 SS-Apple-0147 V4 6 SS-Apple-0148 V5 7 SS-Apple-0149 V4 I would like the results to be listed as follows V4 V5 Apples 3 2 Orange 5 Pear 1 I'm able to sum the number of Apple in column E but I'm unable to sum Apple based on if the apple is in V4 or V5. Here is my last failed attempt: Where "Draft" is the E column Range and Version is the range I defined for column F. This formula just returns the total Apple in column E. Any help would be greatly appreciated. r/ Steve in Arizona =COUNTIF(Draft,"*Apple*")+COUNTIF(Version,V4) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting a mixed text/number column based on text in another colum
ADDIT: The following array formula correctly sums the specified data based on
the entire contents of cells in column E, but I need to be able to count based on the partial TEXT contents of a column E cell, not the entire cell. {=SUM((E2:E3499="SS-ALL-0057")*(G2:G3499="V4"))} What I really wanted was: {=SUM((E2:E3499="*ALL*")*(G2:G3499="V4"))} but it doesn't work. Thank you, in advance for any help. Steve in Arizona "Sierra Vista Steve" wrote: I just can't get the syntax right! I am looking for a formula for analyzing read: summing a spreadsheet function based on the contents in another column. In the example that follows, I need to count Apple, Orange or Pear based on whether Apple, Orange or Pear is in V4 or V5 listed in another column. Column Row E F G 1 SS-Apple-0145 V4 2 SS-Orange-0134 V5 3 SS-Pear-0123 V4 4 SS-Apple-0146 V5 5 SS-Apple-0147 V4 6 SS-Apple-0148 V5 7 SS-Apple-0149 V4 I would like the results to be listed as follows V4 V5 Apples 3 2 Orange 5 Pear 1 I'm able to sum the number of Apple in column E but I'm unable to sum Apple based on if the apple is in V4 or V5. Here is my last failed attempt: Where "Draft" is the E column Range and Version is the range I defined for column F. This formula just returns the total Apple in column E. Any help would be greatly appreciated. r/ Steve in Arizona =COUNTIF(Draft,"*Apple*")+COUNTIF(Version,V4) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting a mixed text/number column based on text in another c
I mispoke, the below formula should have read:
{=SUM((E2:E3499="SS-Apple-0143")*(G2:G3499="V4"))} and {=SUM((E2:E3499="*Apple*")*(G2:G3499="V4"))} "Sierra Vista Steve" wrote: ADDIT: The following array formula correctly sums the specified data based on the entire contents of cells in column E, but I need to be able to count based on the partial TEXT contents of a column E cell, not the entire cell. {=SUM((E2:E3499="SS-ALL-0057")*(G2:G3499="V4"))} What I really wanted was: {=SUM((E2:E3499="*ALL*")*(G2:G3499="V4"))} but it doesn't work. Thank you, in advance for any help. Steve in Arizona "Sierra Vista Steve" wrote: I just can't get the syntax right! I am looking for a formula for analyzing read: summing a spreadsheet function based on the contents in another column. In the example that follows, I need to count Apple, Orange or Pear based on whether Apple, Orange or Pear is in V4 or V5 listed in another column. Column Row E F G 1 SS-Apple-0145 V4 2 SS-Orange-0134 V5 3 SS-Pear-0123 V4 4 SS-Apple-0146 V5 5 SS-Apple-0147 V4 6 SS-Apple-0148 V5 7 SS-Apple-0149 V4 I would like the results to be listed as follows V4 V5 Apples 3 2 Orange 5 Pear 1 I'm able to sum the number of Apple in column E but I'm unable to sum Apple based on if the apple is in V4 or V5. Here is my last failed attempt: Where "Draft" is the E column Range and Version is the range I defined for column F. This formula just returns the total Apple in column E. Any help would be greatly appreciated. r/ Steve in Arizona =COUNTIF(Draft,"*Apple*")+COUNTIF(Version,V4) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting a mixed text/number column based on text in another c
=SUMPRODUCT(--(ISNUMBER(SEARCH("Apple",E2:E3499))),--(G2:G3499="V4"))
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Sierra Vista Steve" wrote in message ... I mispoke, the below formula should have read: {=SUM((E2:E3499="SS-Apple-0143")*(G2:G3499="V4"))} and {=SUM((E2:E3499="*Apple*")*(G2:G3499="V4"))} "Sierra Vista Steve" wrote: ADDIT: The following array formula correctly sums the specified data based on the entire contents of cells in column E, but I need to be able to count based on the partial TEXT contents of a column E cell, not the entire cell. {=SUM((E2:E3499="SS-ALL-0057")*(G2:G3499="V4"))} What I really wanted was: {=SUM((E2:E3499="*ALL*")*(G2:G3499="V4"))} but it doesn't work. Thank you, in advance for any help. Steve in Arizona "Sierra Vista Steve" wrote: I just can't get the syntax right! I am looking for a formula for analyzing read: summing a spreadsheet function based on the contents in another column. In the example that follows, I need to count Apple, Orange or Pear based on whether Apple, Orange or Pear is in V4 or V5 listed in another column. Column Row E F G 1 SS-Apple-0145 V4 2 SS-Orange-0134 V5 3 SS-Pear-0123 V4 4 SS-Apple-0146 V5 5 SS-Apple-0147 V4 6 SS-Apple-0148 V5 7 SS-Apple-0149 V4 I would like the results to be listed as follows V4 V5 Apples 3 2 Orange 5 Pear 1 I'm able to sum the number of Apple in column E but I'm unable to sum Apple based on if the apple is in V4 or V5. Here is my last failed attempt: Where "Draft" is the E column Range and Version is the range I defined for column F. This formula just returns the total Apple in column E. Any help would be greatly appreciated. r/ Steve in Arizona =COUNTIF(Draft,"*Apple*")+COUNTIF(Version,V4) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
formula to calculate a column based on dates in a different colum. | Excel Worksheet Functions | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) |