Remember Me?

#1
December 17th 06, 03:22 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 1
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
December 17th 06, 03:35 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 2
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
December 17th 06, 03:51 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 2
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
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
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
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)

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post SteveDB1 Excel Worksheet Functions 25 June 3rd 09 04:58 PM David Excel Discussion (Misc queries) 1 January 6th 06 03:56 AM Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 02:03 AM Pam Excel Worksheet Functions 1 April 7th 05 07:59 PM 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.