Sum

[ajwelsh]

I have a spreadsheet with the following:

Column A Has codes 000 down to 999. This range includes letters such
as 68A. Each of these codes have values in column B.

I am looking for a formula to calculate the total values in column B
for the codes 000 to 09A.

I used the 'Sumproduct' function, which worked fine without the letters
within the codes, but I need something that can calculate the sum with
the letters.

Can anyone help me please?

I attach a sample file

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=409217

 

[Mark Graesser]

ajwelsh,
As long as you don't have any letters over "f", you can fake out excel by converting them to hexadecimal.

1) Enter =HEX2DEC(A6) in cell B6 and copy down.
If you get a NAME? error you need to install the analysis toolpack. Search on analysis toolpack in the excel help files for directions.

2) Assuming the min number in E1 and the max number in F1 then use:
=SUMPRODUCT((B6:B37>=HEX2DEC(E6))*(B6:B37<=HEX2DEC(E7))*(C6:C37))


Good Luck,
Mark Graesser
(e-mail address removed)


----- ajwelsh > wrote: -----

I have a spreadsheet with the following:

Column A Has codes 000 down to 999. This range includes letters such
as 68A. Each of these codes have values in column B.

I am looking for a formula to calculate the total values in column B
for the codes 000 to 09A.

I used the 'Sumproduct' function, which worked fine without the letters
within the codes, but I need something that can calculate the sum with
the letters.

Can anyone help me please?

I attach a sample file

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=409217

 

[ajwelsh]

Sorry, can't get it to work using that.

Any other suggestions

 

[Frank Kabel]

Hi
maybe you can explain your problem with a little more detail. As Mark
mentioned his solution will work only for hexedecimal values. So if
your column A has a code like '00X' it won' work (your are only allowed
to use charcater A-F).

Assuming that you only want a sum of adjacent cells (as suggested by
your example) maybe the follwoing formula could help
=SUM(OFFSET($B$1,0,0,MATCH("00A",$A$1:$A$9999),1))

HTH
Frank

 

[Mark Graesser]

ajwelsh,
I forgot to say you needed to insert a column between A and B, so you would have a new blank column B to enter the formula in. Was this the problem you ran into?

Do you have the analysis toolpack installed?

Do you have letters above f?

What wouldn't work? value error? incorrect total? returning zero?

I performed this procedure on your attached file and had it working, so I'm pretty sure we can get it going for you. Let me know.

Regards,
Mark Graesser
(e-mail address removed)

----- ajwelsh > wrote: -----

Sorry, can't get it to work using that.

Any other suggestions?

 

[ajwelsh]

I attach another example. On B1 on the 'Range Values' sheet, I need th
total values for codes 000 to 09Z from the 'Data' sheet.

There could be more codes, so I need it to look up Column A, find 00
to 09Z and then return total for the year.


Hope this helps?

Please help.

Thank

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=41013

 

[Frank Kabel]

Hi

i just adapted my example in a previous post to your Excel Sheet. Enter
=SUM(OFFSET($C$1,0,0,MATCH("100",$A$1:$A$9999),5))
To get the total for '000' - '100' over all 5 columns of data

HTH
Frank

 

[Mark Graesser]

ajwelsh,
Two more ideas.

1) I was thinking you could use the following formula if your table was sorted:

=SUM(INDIRECT("C"&MATCH("000",A:A)&":C"&MATCH("09Z",A:A)))

But I see that 09Z is not in the table so a match won't be found. I included this in case it gave you some other ideas.

2) Similar to my HEX2DEC option. In a helper row you could use a formula like:

=IF(ISNUMBER(VALUE(RIGHT(A1,1))),VALUE(A1&"00"),VALUE(LEFT(A1,2)&VLOOKUP(RIGHT(A1,1),Sheet3!$A$1:$B$26,2,0)))

This looks like a mess but it will change your 3 digit numbers into 5 digit numbers. All normal numbers will get a 00 added on. All letters will be replaced by 9 and two digits for the letter (ex. 09Z > 09926)

You will need to create a reference table on sheet three. Starting in A1 enter "A" in A2 enter "901". In B1 and B2 enter "B" and 902, and so on.

Now you can use a variation of the SUMPRODUCT formula previously given.

Hopefully one of these options will get you what you need.

Good Luck,
Mark Graesser

----- ajwelsh > wrote: -----

I attach another example. On B1 on the 'Range Values' sheet, I need the
total values for codes 000 to 09Z from the 'Data' sheet.

There could be more codes, so I need it to look up Column A, find 000
to 09Z and then return total for the year.


Hope this helps?

Please help.

Thanks

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=410133