Counting using two sets of criteria

[Spiketrip]

There was some confusion on what I was trying to accomplish in a
previous post...so I have included a small table as an example.

Date Profit/Loss YTD P/L Start Bal. End Bal. Source

1/04 (200) (200) 10000 9800
LDR
1/14 (100) (300) 9800 9700
House
1/16 200 (100) 10000 9900
QQQ
1/18 500 400 9900 10400
House
1/30 (200) 200 10400 10200
LDR


I would like to count the number of profits for the House Source, QQQ,
LDR individually. I would also like to count the losses for each.

Profits for QQQ: 1
Losses for QQQ: 0
Profit for LDR: 0
Losses for LDR: 2
Profits for House: 1
Losses for House: 1

Thanks for any help. I have been trying to use COUNTIF but it only
accepts one set of criteria.

Mike

 

[JE McGimpsey]

One way:

Profits for QQQ:

=SUMPRODUCT(--(C2:C6>0),--(F2:F6="QQQ"))

Losses for LDR:

=SUMPRODUCT(--(C2:C6<0),--(F2:F6="LDR"))

etc.

 

[Spiketrip]

JE,

Thanks for the suggestion. Will SUMPRODUCT count the number of losse
or add them together? I am trying to count the number..1,2,3,4,5,6,
and then rpeort the total.

Thanksagain, Mik

 

[Harlan Grove]

Thanks for the suggestion. Will SUMPRODUCT count the number of losses
or add them together? I am trying to count the number..1,2,3,4,5,6,7
and then rpeort the total.

Why not just try JE's formula and find out?

 

[Spiketrip]

I did an it didn't work...came back with #NUM

 

[Harlan Grove]

I did an it didn't work...came back with #NUM

Here are JE's formulas:

Profits for QQQ:

=SUMPRODUCT(--(C2:C6>0),--(F2:F6="QQQ"))

Losses for LDR:

=SUMPRODUCT(--(C2:C6<0),--(F2:F6="LDR"))

SUMPRODUCT *NEVER* returns #NUM! unless one of the values passed to it is
#NUM!. In the formulas above, that could *ONLY* be the case if either C2:C6
or F2:F6 contained one or more cells evaluating to #NUM!. Either you made a
mistake adapting these formulas to your actual ranges, or your actual ranges
contain #NUM! errors. THERE'S NO OTHER ALTERNATIVE.

So, what are the *EXACT* formulas you're using?

 

[Harlan Grove]

....
SUMPRODUCT *NEVER* returns #NUM! unless one of the values passed to it is
#NUM!. . . .

Nope. I'm wrong. It returns #NUM! when you reference entire columns, as in

=SUMPRODUCT((A:A>0)*(B:B="XYZ"))

That's one draw-back to SUMPRODUCT compared to SUMIF or SUM(IF(.)) (at least
when the last one is *not* array-entered). SUMPRODUCT works on arrays. Even
if given a range as an argument, it treats it as an array. Excel has several
restrictions on array sizes, and one of them is that no dimension may exceed
65,535 entries. Entire columns are 65,536 rows/entries.

So, you may need to modify your formulas to use less than entire column
ranges. Once you've done so, you should find JE's formulas work.

 

[Spiketrip]

I tried again by entering values for the array. Still doesn't work.
here is a test table I did in excel. Every combination <0,>0, LDR
House, etc. comes up 0.

Any other suggestions.

Thanks again for all of the suggestions.

Mike

Date P/L P/L YTD Start Bal End Bal Source
4-Jan -200 -200 100.00 -100.00 LDR
14-Jan -100 -300 -100.00 -200.00 House
16-Jan 200 -100 -200.00 0.00 QQQ
18-Jan 500 400 0.00 500.00 House
30-Jan -200 200 500.00 300.00 LDR


=SUMPRODUCT(--(C38:C42<0),--(F38:F42="LDR")

 

[Spiketrip]

I still have not been able to solve this problem. Any help would b
appreciated. Thanks to all who have tried.

Mik

 

[Harlan Grove]

I tried again by entering values for the array. Still doesn't work.
here is a test table I did in excel. Every combination <0,>0, LDR,
House, etc. comes up 0.

Any other suggestions.

Thanks again for all of the suggestions.

Mike

Date P/L P/L YTD Start Bal End Bal Source
4-Jan -200 -200 100.00 -100.00 LDR
14-Jan -100 -300 -100.00 -200.00 House
16-Jan 200 -100 -200.00 0.00 QQQ
18-Jan 500 400 0.00 500.00 House
30-Jan -200 200 500.00 300.00 LDR


=SUMPRODUCT(--(C38:C42<0),--(F38:F42="LDR"))

IF the data above were in A37:F42 (with the column headers in A37:F37), then one
possible reason (and the only other one I can think of) why the formula above
returns zero is that there's garbage (such as trailing spaces) in the column F
entries.

Try these formulas first.

=COUNTIF(C38:C42,"<0")

=COUNTIF(F38:F42,"LDR")

If they both return positive values, then I'm stumped. If either returns zero,
then there's garbage in your data. If the first returns zero, then your numbers
are actually text, in which case you could copy a blank cell, then select the
col C values and paset-special, ADD. If the second returns zero, then you have
either stray trailing ASCII spaces or nonbreaking spaces in col F entries.
Either use Edit > Replace to remove them or change the SUMPRODUCT formula to

=SUMPRODUCT(--(C38:C42<0),--(TRIM(SUBSTITUTE(F38:F42,CHAR(160),""))="LDR"))