SUMPRODUCT or SUMIFS?

[Keibri]

Hi all,

I was wondering whether someone can help me with the below.

Basically as one can see from the attached I have two columns: Column A is made up of 'nominal codes' and Column B is made up of 'Amounts'

I would like to make a formula which would sum all the amounts in column B, however, only those amounts which have a nominal code which starts with a '5', '6', '7', '8', '9'

Thanks a lot, appreciate your kind help!

Keibri

[Roger Govier]

Hi
I would just add a helper column - C
in C2
=--Left(A2,1) and copy down. The "--" double unary minus converts text to numeric.
Then to get the total it is just
=SUMIFS($B$2:$B$144,$C$2:$C$144,">"&5)

If you do have to use SUMIF, then it would be =SUMIF($C$2:$C$144,">"&5,$B$2:$B$144)

I much prefer the layout of Sumifs, where ou give the range to be summed first, and can have multiple criteria follwoing.

[underscoreBadger]

Hi there.

As your nominal codes column is formatted as numbers, you could use a simple formula like

Please Login or Register  to view this content.

However, if you anticipate having alpha characters in your nominal codes, then convert that column to Text format and you can use a more contrived formula like

Please Login or Register  to view this content.

[Pete_UK]

Or, without changing anything, you can use this formula:

=SUMPRODUCT(B2:B144,--(--(LEFT(A2:A144))>=5))

Change the 144 if your data may increase in length.

Hope this helps.

Pete

[Jonmo1]

This

=SUMIF(A:A,">50000000",B:B)

But > would have to be >=

[underscoreBadger]

Good point @Jonmo1 thanks for spotting that!

[Keibri]

Cheers guys!!! Thanks a lot for your suggestions. All suggestions worked out smoothly!