Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 17th 06, 03:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 1
Default 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   Report Post  
Old December 17th 06, 03:35 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 2
Default 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   Report Post  
Old December 17th 06, 03:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 2
Default 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   Report Post  
Old December 17th 06, 05:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 1,726
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct issues SteveDB1 Excel Worksheet Functions 25 June 3rd 09 04:58 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 03:56 AM
Positioning Numeric Values Resulting from 6 Column Array Formula Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 02:03 AM
formula to calculate a column based on dates in a different colum. Pam Excel Worksheet Functions 1 April 7th 05 07:59 PM
Putting text in a column based on variable text from another colum Jacky D. Excel Discussion (Misc queries) 1 December 16th 04 06:09 PM


All times are GMT +1. The time now is 11:57 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017