countif formula?

[Spott]

Here's an abbreviated sample data from my sheet. There are 149 entries and I'm trying to pull out 6 for one of my summary sheet fields.

Shift Class_name
D1 Sigma
D2 Sigma
D1 Evaporator
D2 Evaporator
N1 Evaporator
D1 Sigma

If shift = "D1", then count number of cells containing "*Sigma*"

I need to nest something, here's my current formula:
=COUNTIF('Metals L2'!E$1:E$1131,"*sigma*")
which returns all sigma, not filtering other shifts out. The variations I've tried have added or multipied the 2 variables.

Am I in the right formula neigborhood?

Thanks for any help on this.

 

[John McGraw]

On 2002-04-22 18:41, Spott wrote:
If shift = "D1", then count number of cells containing "*Sigma*"

I need to nest something, here's my current formula:
=COUNTIF('Metals L2'!E$1:E$1131,"*sigma*")
which returns all sigma, not filtering other shifts out. The variations I've tried have added or multipied the 2 variables.

Am I in the right formula neigborhood?

Thanks for any help on this.

Assuming "Shift" is column A and "Class Name" is column B;
Try this:
=SUMPRODUCT((A2:A7="D2")*(B2:B7="sigma"))
This message was edited by John McGraw on 2002-04-22 19:15

 

[Spott]

So far no luck. These are the formulas I've tried:
=COUNT(IF('Metals L2'!E$2:E$999="*sigma*",IF('Metals L2'!A$2:A$999="D1",0)))

=SUM(('Metals L2'!E$2:E$999="*sigma*")*('Metals L2'!A$2:A$999="D1"))

=SUMPRODUCT(('Metals L2'!A$2:A$999="D1")*('Metals L2'!E$2:E$999="*sigma*"))

=SUM(('Metals L2'!E2:E999="*sigma*")*('Metals L2'!A2:A999="D1"))

The result should be 6, these formulas all return 0. I think I entered them all as arrays (CSE)but my brain is getting very tired...
Thanks!

 

[Aladin Akyurek]

On 2002-04-23 12:53, Spott wrote:
So far no luck. These are the formulas I've tried:
=COUNT(IF('Metals L2'!E$2:E$999="*sigma*",IF('Metals L2'!A$2:A$999="D1",0)))

=SUM(('Metals L2'!E$2:E$999="*sigma*")*('Metals L2'!A$2:A$999="D1"))

=SUMPRODUCT(('Metals L2'!A$2:A$999="D1")*('Metals L2'!E$2:E$999="*sigma*"))

=SUM(('Metals L2'!E2:E999="*sigma*")*('Metals L2'!A2:A999="D1"))

The result should be 6, these formulas all return 0. I think I entered them all as arrays (CSE)but my brain is getting very tired...
Thanks!

Try:

=SUMPRODUCT((ISNUMBER(SEARCH("sigma",'Metals L2'!E$2:E$999)))*('Metals L2'!A$2:A$999="D1"))

Note 1. If "sigma" is not a substring occurring in different combinations, then as John suggested, just use:

=SUMPRODUCT(('Metals L2'!E$2:E$999="sigma")*('Metals L2'!A$2:A$999="D1"))

Note 2. You can put these criteria in cells of their own and use the cell refs in the formula.

Aladin
This message was edited by Aladin Akyurek on 2002-04-23 13:01