COUNTIF and SUMIF Functions when there are 2 criteria

  • Thread starter Thread starter deacs
  • Start date Start date
D

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
 
deacs said:
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
Click to expand...

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