Thanks:  0
Likes:  0

1. 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.

2. 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 ]

3. 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!

4. 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.

[ This Message was edited by: Aladin Akyurek on 2002-04-23 13:01 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•