SUMPRODUCT calcs....

**Somebodyhadto** · 06-15-2008, 03:21 AM

To the brains out there...

In the attached, you can see that I am wanting a formula to show accumulating profits for a certain month AND for a certain ‘type’ of trade. DT would be a ‘day-trade’, for example. This being the case…

I need a formula for N4 that shows the total profits (referencing column H) for the month of June (referencing column I) but ONLY for trades labeled ‘DT’ (as per B column). I'm fairly certain that it's a ‘SUMPRODUCT’ formula that I’ve used before, but I can’t get the syntax. Once one of you provides it, I realize it’s a matter of copying the syntax for all other trade types and as relates to what month they’re occurring in, et al.

Thanks very much and look forward to your solution;

Somebodyhadto.

Martin B.

**VBA Noob** · 06-15-2008, 03:26 AM

Try

=SUMPRODUCT((B4:B200="DT")*(MONTH(I4:I200)=6)*(H4:H200))

VBA Noob

**Shijesh Kumar** · 06-15-2008, 03:34 AM

Hi,

I have adjusted a brackted which i thought was at wrong place
Hope its gives the correct value.

Please Login or Register  to view this content.

**Somebodyhadto** · 06-15-2008, 05:00 PM

For the life of me, I don't get it.

On the sample sheet, the formula(s) worked fine. But when I attempted to use the same formula on the REAL workbook I devised, I get the #VALUE thing happening (if I try it on the first worksheet somewhere) and on the intended target worksheet, I doesn't even recognize the calcs as a formula.

Here's what I have going into the 2nd worksheet "Monthly Trade Tracking 2008" in Cell G4

=SUMPRODUCT(--(Trade Tracking Spreadsheet 2008!C4:C2000="DT"),--(Trade Tracking Spreadsheet 2008!L4:L2000),--(Trade Tracking Spreadsheet 2008!MONTH(M4:M2000)=6))

We're almost there, I just know it.

Thanks again...look forward to your solution(s).

Take care,

Martin B.

**VBA Noob** · 06-15-2008, 06:13 PM

Change the formula in H4 to

=IF(ISBLANK(G4),0,SUM(G4*D4)+D4)

then use

=SUMPRODUCT(--(MONTH('Trade Tracking Spreadsheet 2008'!M4:M2000)=6)*('Trade Tracking Spreadsheet 2008'!C4:C2000="DT")*('Trade Tracking Spreadsheet 2008'!H4:H2000))

HTH

VBA Noob

**Somebodyhadto** · 06-15-2008, 08:03 PM

Hi there VBA Noob.

I don't see what that formula you suggested is supposed to refer to and unfortunately it doesn't calculate right. You ARE referring to my most recent workbook I hope?

Cell G4 of the 'Monthly Trade Tracking 2008' must add up ONLY the values in column L of the 'Trade Tracking Spreadsheet 2008' as it relates to June AND as it relates to being "DT" or not.

The total that should then appear in cell G4 of the 'Monthly Trade Tracking 2008' sheet is $674.00

I'm very grateful for your efforts and if you get it, great! I think, now, you know where I'm heading. I wonder what's throwing things off?

Martin

**rylo** · 06-15-2008, 08:29 PM

Martin

Try
'monthly trade tracking 2008'!G4:
=SUMPRODUCT(--('Trade Tracking Spreadsheet 2008'!C4:C2000="DT"),('Trade Tracking Spreadsheet 2008'!L4:L2000),--(MONTH('Trade Tracking Spreadsheet 2008'!M4:M2000)=6))

rylo