sumif or countif not sure

A

Ann

i want to sum the numbers in column B based on the criteria in column A and
give a count. not sure what the combo is.

Col A col b
1 9.99
1 9.99
2 10.00
2 30.00

so i want the result to show
1 19.98
2 40.00
 
A

Ann

sorry let me be more clear. column A will say activation fee and col b will
have either 9.99 or 29.99 so if auth/9.99 then i want it to sum those and if
auth/29.99 then i want it to sum those separately.
 
M

Mike H

Ann,

That is considerably less clear than the first question.

Col A = Activation fee. Straightforward text, i'm relaxed about that.
Col B = 9.99 or 29.99. We can't get mixed up over that, 1 of 2 values.

If auth/9.99 or auth/29.99 is very unclear and I'm lost.
Please re-clarify

Mike
 
A

Ann

we have two types of auth fees, so we need a count of the ones which are 9.99
and 29.99
so i col a is auth fee
in col B is 9.99 or 29.99
 
M

Mike H

Could be:-

=COUNTIF(B:B,9.99) & " of 9.99 fees totalling "&SUMIF(B:B,"=9.99",B:B)&"
Dollars."

Substitute 9.99 * 29.99 for the other authorisation fee.

Mike
 
A

Ann

that won't work since there's other types of fees which equals 9.99 . here's
the entire spreadsheet layout

col A col B
Authfee 9.99
tranfee 9.99
authfee 9.99
authfee 29.99

so i want the totals to be
9.99 auth fees, 2 =19.98
29.99 auth fees, 1 = 29.99
 
P

Peo Sjoblom

=SUMIF(A:A,"authfee",B:B)

change accordingly for the tranfee or use

=SUMIF(A:A,C2,B:B)

where you put the criteria in C2

to count them

=COUNTIF(A:A,"authfee")
 
S

Sandy Mann

Assuming that you have headers in Row 1, try:

=SUMPRODUCT((A2:A5="Authfee")*(B2:B5=9.99)*9.99)

Alter the "Authfee" & 9.99 for other totals.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
A

Ann

peo - i want a total of 2 variables, not 1.
if authfee and 9.99 then i want a total and a count
if authfee and 29.99 then i want a total and a count
 
S

Sandy Mann

For a count use:

=SUMPRODUCT((A2:A5="Authfee")*(B2:B5=9.99))


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
P

Peo Sjoblom

=SUMPRODUCT(--(A2:A10="authfee"),--(B2:B10=9.99))

for count

for total

=SUMPRODUCT(--(A2:A10="authfee"),--(B2:B10=9.99))*9.99
 
Top