summing question

[C-Money]

I'm working on a summary spreadsheet, and I've created a
table breaking down Gains and Losses. The table has 6
rows and 6 columns with the column headers 1-500, 501-
1000, 1001-2000, 2001-5000, 5001-10000, 10001+ In this
table I used this counting formula to populate the cells

=SUMPRODUCT((NE!$N$3:$N$174>-500)*(NE!$N$3:$N$174<-1)*(NE!
$J$3:$J$174<=0))

Now I have to create a table summing the actual values of
the cells that met the criteria in the SUMPRODUCT formula.
I know that SUMIF does something like this; however I want
to sum up the cells in the J row after meeting the three
criteria in the sumproduct formula. I'm not sure if/how I
would use the SUMIF formula. Any suggestions?

 

[Bob Phillips]

=SUMPRODUCT((NE!$N$3:$N$174>-500)*(NE!$N$3:$N$174<-1)*(NE!
$J$3:$J$174<=0),(NE!$N$3:$N$174))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks for the help. How come I would have to reference
the N column when I really want to add the information in
the J column. I'm kinda confused on how the SUMPRODUCT
formula works in general. I found out how to do the
counting via exceltip.com but I don't understand how/why
it does what it does.

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

My mistake, I copied the wrong part of your original format. Of course it
should be

=SUMPRODUCT((NE!$N$3:$N$174>-500)*(NE!$N$3:$N$174<-1)*(NE!$J$3:$J$174<=0),(N
E!$J$3:$J$174))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)