Calculating the Total for NEgative and Posite Amounts for same currency
Dear ppl
Can you please help we out. From the two columns below you can see that one columns represents a Currency and the second column Amount. I would like to total for a Single Currency the Sum of all +ve Amounts and Sum of all Negative Amounts.
-----------------------------
Current Data File
-----------------------------
GBP 161.41
GBP 22.58
GBP 24.07
GBP 29,531.41
GBP 16,912.67
GBP 1,602.32
GBP 200
GBP -11345
GBP 541
GBP -41,101.28
GBP 23035
GBP 329,811.79
JPY -423,898.81
JPY -4,135.45
SAR -42558
SAR 89045
SDD -20,444.02
USD 7,024.77
USD 10
USD 310.23
USD 707,979.18
USD 68.51
------------------------------------
Expected Output in a separate Sheet
------------------------------------
GBP +ve 10000
-ve 200
JPY +ve 12.23
-ve 1214.50
-------------------------------
How can I achieve it ??
hi
I didn't get any output at all...Where exactly shud I type this formula ?? I input your formula at E1.
Sum Negative and GPB
try this;
Type the following in E1:
=SUMPRODUCT(--(A1:A22="GPB"),--(B1:B22<0),B1:B22)
AP
Assuming the data are in
Assuming the data are in A1:B22 then
{=SUMIF(A1:B22,"GBP",B1:B22)}
would sum GBP only.
when you enter the formula don't forget to hit CTRL-SHIFT-ENTER as it's an array formula...Note the curly brackets.