SUMIF

[hans]

I have a spreadsheet with two columns, A containing
account numbers (all have 6 numbers), B the amount
outstanding on them. I want to have a formula that shows
the sum of B for a selected group of account numbers, for
example all account numbers higer than 500000. I can't
this with a normal SUM formula, because new account
numbers are added all the time.

I tried to use the following SUMIF formula:
=SUMIF(A5:A200,>"501000",G5:G200)
but it always gives a zero. When i remove the > sign,
however, it shows the balance outstanding on number 501000.

Does anyone know to correctly use this (or another)
formula so that it shows ALL balances for accountnumber.
501000 and higher?

Thanks in advance
Hans

 

[Norman Harker]

Hi Hans!

Use (eg)
=SUMIF(A5:A200,">"&501000,G5:G200)

 

[Hans]

Hi Norman

It still gives a zero as result.

regards,
Hans

 

[Norman Harker]

Hi Hans!

That's most likely the result of your account numbers being formatted
as text and not numbers.

Change them to number by:

Select an empty cell
Copy
Select the account numbers
Edit > Paste Special > Add > OK

 

[papou]

Hi hans
Try array formula: (validate with Ctrl + Shift + Enter)
=SUM(IF(A5:A200>"501000",G5:G200,0))
Or using SUMPRODUCT:
=SUMPRODUCT((A5:A200>501000)*(G5:G200))

Cordially
Pascal

 

[Bob Phillips]

Hans,

On the chance that your values may be strings, try

=SUMIF(A5:A200,">""501000",G5:G200)

--

HTH

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

 

[Hans]

Hi Norman

Unfortunately, I cannot do that because the account
numbers column is linked to another sheet. I tried to
format the column to numbers already, but that didnt help.
It probably has to do with the fact that the account
numbers columns is linked to a pivot table that in its
turn is linked to an external database - for some reason
this seem to change the format of the cells and makes it
not possible to make some formula's work (i have had this
problem before).

Anyway, many thanks for your help - ill see if i can find
a way to work around this.

kind regards,
Hans

 

[Norman Harker]

Hi Hans!

I agree. I don't like suggesting a change to your data anyway.

Two suggestions:

Array entered formula:
=SUM(IF(A5:A200>"501000",G5:G200,0))
Entered by pressing and holding down Ctrl + Shift and then pressing
Enter.

This appears not to "mind" if your numbers are text form.

Set up a helper column in say column Z

Z5:
=A5*1
Copy down

Then you can use:
=SUMIF(Z5:Z200,">"&501000,G5:G200)

Both methods leave your original data in column A intact.

 

[Soo Cheon Jheong]

Hans,

=SUMPRODUCT((VALUE(A5:A200)>501000)*G5:G200)


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[Norman Harker]

Hi Soo Cheon Jheong!

Or:
=SUMPRODUCT((--(A5:A200)>501000)*G5:G200)

 

[hans]

Hi Norman and Soo

The SUM(IF...) and SUMPRODUCT formula both work! Thanks
for your help.

Kind regards
Hans



-----Original Message-----

 

[hans]

Hi Norman and Soo

The SUM(IF...) and SUMPRODUCT formula both work! Thanks
for your help.

Kind regards
Hans



-----Original Message-----