Add up Plus & Minus Figures separately by Location

[John]

I have 3 columns of Data, Location, Date and Stock Variances. I wish to Add
up The total PLUS Stock Variances per Location in one cell and the MINUS
Stock Variances per Location in another cell.

My data is variable in length, thus I will assign Range Names to the 3
columns. I am not sure how I can add up and distinguish both Location and
whether the Stock variance is either Positive or negative.


Thanks

 

[Guest]

John,

You can try this:

=SUMPRODUCT(--(varianceRange<0)*(locationRange=LocationCodeOrName),varianceRange)

Will add all variance smaller than zero for a given location.

Regards,
Robert

 

[John]

Excellent Robert, exactly what I needed

Thanks