Find rows based upon TWO other columns, without Data Filter

[NOYGDB]

Ok, here's one that's got me stumped:

In Column B, I've got percentage numbers. In Column F, I've got several pre-designated text blurbs.

I'd like to count the number of rows that are positive or negative in Column B, but only if there is a specific text blurb in Column F.

For instance, here's the first line of the spreadsheet:
Symbol: % Chg: Net Chg: Counter: Industry: Sector:
AA -6.36% -2.19 5 Metal Mining Basic Materials

So, since Column B is negative, and Column F is "Basic Materials", I'd increment the 'negative' count for Basic Materials by one.

This will allow me to keep track of the number of stocks that are positive or negative for the day, in each Sector or Industry.

Any ideas?

[machinegun]

can supply a few more rows of data that match/do not match your criteria?

[jrkrideau]

[David]

You might use some [hidden] helper column[s], say column H. Let G1 contain the text in column F being searched. You can expand on this as necessary [last comment below].

I don't have time at the moment to outline everything, but you'd have something like =IF(AND(B1<0;F1=G1);1;"") so indicating a "1" when the negative condition is met. Do another for the positive. Hide columns not needed for report.

A counter cell, say M1, can keep track of the number of incidences by summing this hidden column of negative condition with =SUM(H1:H100) or something like that.

If necessary, you can list the sector criteria in one column with the summed values in an adjacent column. ...

David.

[NOYGDB]

All the columns are cut-and-pasted from TradeStation... the counter is a quick-reference way of telling if a stock meets all the parameters that I'm looking for. It can be ignored... it was just easier to include it in the spreadsheet, so I could do a quick cut-and-paste from TradeStation to the spreadsheet, without jiggering with columns and layout each time.

Ok, here's the first few lines:
Symbol: % Chg: Net Chg: Counter: Industry: Sector
AA -6.36% -2.19 5 Metal Mining Basic Materials
AAU 2.40% 0.04 4 Gold & Silver Basic Materials
AAII -17.69% -0.32 3 Aerospace & Defense Capital Goods
ABB -3.79% -1.06 5 Conglomerates Conglomerates
ABAT -6.88% -0.4 4 Appliance & Tool Consumer Cyclical
AAV -2.82% -0.36 3 Oil & Gas Operations Energy
AAME 3.12% 0.05 4 Insurance (Life) Financial

So, as you can see, in Columns E and F, there can be any of several different (but known) text blurbs.

What I'd like to do, for instance, is to have two counters... one for stock symbols in a given Sector or Industry that are positive on the day, and one for those that are negative.

Thus, for the Sector of "Basic Materials", it would see stock symbol AA is down for the day, and would increment the "down stocks' counter by one. It would also see that stock symbol AAU is up for the day, and would increment the 'up stocks' counter by one... but ONLY for the stocks that are in the 'Basic Materials' Sector.

I would use different cells to get the price up / price down data for each of the Sectors or Industries.

Thus, I can compare up : down stocks in any given Sector or Industry quickly.

It would be something like this:
=COUNTIF(AND(E3:E10000;"Chemical Manufacturing";B3:B10000;">0"))
or
=COUNTIF(AND(F3:F10000;"Basic Materials";B3:B10000;">0"))

But that doesn't work.

[NOYGDB]

Oh, wait... I think I can use DCOUNTA. Give me a bit to experiment.

{EDIT}
Eh... no. You need a label row above each data row for DCOUNTA... meaning I'd have to have alternating data and label rows... I could do that, and hide the label rows, I guess, but it's such a kludge.

Any other ideas?

[NOYGDB]

Ok, so I went with the DCOUNTA idea above... and it works, although it IS a kludge.