COUNTIF and SUMIF Functions when there are 2 criteria

[deacs]

Hi,

I’m trying to formulate a COUNTIF and SUMIF function that satisfies 2
criteria. Is there any way to do this? An example of what I’m trying
to do is as follows:
Column A contains names of financial markets (stocks, currencies,
commodities,…)
Column B contains profit or loss per market

In Column C I’d like to count the number of times each market (Column
B) returns a profit (cell > 0)
In Column D I’d like to sum the cells in Column B that are positive for
each market (Column A)

What is the most efficient way to do this? I found a work around that
resulted in increasing the size of the spreadsheet to a point where I
would prefer it not be (went from 2 mb up to a little over 24mb).

Any help would be greatly appreciated.

Best,
Jimmy

 

[Paul]

Hi,

I'm trying to formulate a COUNTIF and SUMIF function that satisfies 2
criteria. Is there any way to do this? An example of what I'm trying
to do is as follows:
Column A contains names of financial markets (stocks, currencies,
commodities,.)
Column B contains profit or loss per market

In Column C I'd like to count the number of times each market (Column
B) returns a profit (cell > 0)
In Column D I'd like to sum the cells in Column B that are positive for
each market (Column A)

What is the most efficient way to do this? I found a work around that
resulted in increasing the size of the spreadsheet to a point where I
would prefer it not be (went from 2 mb up to a little over 24mb).

Any help would be greatly appreciated.

Best,
Jimmy

Something like
=SUMPRODUCT((A1:A100="Market1")*(B1:B100>0))
for counting, and
=SUMPRODUCT((A1:A100="Market1")*(B1:B100>0)*(B1:B100))
for summing.

 

[Don Guillett]

try this idea

=SUMPRODUCT((D110="a")*(E1:E10>1))

 

[deacs]

Bingo! You guys rock! Thanks for your help.

Jimmy