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)

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)

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)

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)