SUMIF with text - Excel does not sum range?!

Hi,

I am trying to SUMIF some statistics in a column when the range contains a certain two-letter abbreviation.


My equation is as follows =SUMIF(Y409:Y3, "MC", AA409:AA3)

This should be simple enough, sum the figures in cells AA409:AA3 if the range (Y409:Y3) contains the words "MC" or similar two letter abbreviation.

Any ideas why excel is displaying the error?

Many thanks!

Hi Ron,

There are indeed trailing spaces...could that be what is causing the problem?

I am getting a HASHNUM! error as my equation return.

The Y Column is populated with two letter abbreviations for each project. The AA column is strictly numerical however a value is only returned in this column depending criteria based on several other associated equations....if this makes sense?

There are as you suggested, trailing spaces between the rows.

Chris


Thank you both for your help!

Hi Ron, Joseph,

Unfortunately as the stats in each cell of column AA (and so on ie AB, AC) each have their own formula that generates a value owing to certain criteria, then I cannot replace them with 0's.

@Joseph - Both of those equations seemed to start pulling values but it did not sum all the values required from Column AA?

Thank you both for your help, I really appreciate this.

Chris

Alas I have tried using the Wild Card option! The Sum of the values is still too small..which suggests that some values are not being counted?

When I try a simple SUM function on a couple of cells in the AA column that are populated with numbers/values and not just #NUM! I still get the response #NUM!....does this help explain anything?

Hi,

I have cut out a small portion of the S/S, this includes the range/criteria I want met in order to sum, the sum range, and the date cells that in this instance are irrelevant, but form part of the wider equation that dictates whether the BLUE columns generate a count/number.

Hopefully this helps....as I have another 399 cells of this sheet above to sum.....and I cannot run any risk solver scenarios until they are!

Much appreciated,

Chris

Hi Ron,


I have attached a slightly larger sample. I cannot attach much more I am afraid but you get the gist of what I am trying to do here.


The Blue column is populated by a number (usually 1-5) however this is dependent on a date.

You will see the blue column is populated by IF formulas (The missing/broken part will be a cell which says JAN 13) which forms part of the Orange date columns.

I want to be able to Total/SUM the blue column and its Constituents IE all the total for those populated with MC and GW and etc.

Does this help?

Many thanks once again!

Hi Ron,

Thank you for your response,

I have applied the formula to my spreadsheet, as I just wish to return the sum of the individual cells in each column which belong to MC and the same for GW (and many many more!)

I have applied your formula which looks like this now:

=SUM(SUMIFS(AA3:AA410,Y3:Y410,{"MC"},AA3:AA410,">=0"))

To return the values that are >0 (forgot about that one) however it still returns with the wrong sum?

Ok,


Another tract - When I try to simply add up the numbers >0 in the blue columns, I still get either A) #NUM! or B) It adds SOME of the numbers but the true total.

I have attached the top portion of my spreadsheet - For example, I want to Sum (Seen at the bottom) all the numbers in column AA that are related to MC....Yet I always receive the same error?

Hi Ron,

Yes that worked out well on my sample......the problem is when I transfer it to my main spreadsheet and alter the values to reflect the ranges AA3:AA411 and Y3:Y411 etc it still fails to show the correct sum?

Perhaps If I attach the entire spreadsheet (I apologise for being such a pain) maybe you might be able to spot where the bug lies?

Thank you very much for your help Ron

Originally Posted by JosephP

you haven't made the correct ranges absolute. in AA418 enter
=SUMIFS(AA$3:AA$411,AA$3:AA$411,"<>#NUM!",$Y$3:$Y$411, "*"&$X418&"*")
then copy across and down

Hi Joseph,

Thank you for your response....I have done as you have said however the sum is still incorrect....the AA1 column for MC alone should have a total of around 5.5?

Any ideas?


Many thanks,

Chris

Originally Posted by JosephP

some of the numbers in column Z are actually stored as text-if you convert them all to numbers the sums work

Forgive me if I have missed a trick, the formula does not take reference from Z?

Thanks,

Chris

Originally Posted by JosephP

the formula in the AA column does ;-)

Apologies! Yes it does, however I have formatted the entire column to number however sum is still incorrect....can I ask what number It returns for you (for MC) in column AA?

Thank you!

Originally Posted by JosephP

changing format does not affect what is stored. you need to convert the numbers in column Z into real numbers-for instance by formatting the cells as general then copying a blank cell and paste special-values-add over the column Z data

see attached

Joseph,

Thank you so much for your help on this, this has helped me massively!! Such a relief to get this out of the way. I have learnt a lot from this!

Originally Posted by JosephP

changing format does not affect what is stored. you need to convert the numbers in column Z into real numbers-for instance by formatting the cells as general then copying a blank cell and paste special-values-add over the column Z data

see attached

Joseph,

Thank you so much for your help on this, this has helped me massively!! Such a relief to get this out of the way. I have learnt a lot from this!

Noted!

Thank you once again for your help and thank you to Ron as well.

Chris

Hi Joseph, Ron,


VERY Sorry to come back to you on this, Everything appeared to work and with your help, I thought I had de bugged this!

I have put dates in L29:L31 and Values to be counted in Z29:Z31

These values that are counted show up in the correct date tabs furtheralong (AL29, AM29 & AN29) and so on....HOWEVER these sums (100 just for ease) have not been summed at the bottom? Under MC?

If you could cast your eye over this (if it is simple) I would be very grateful once again!

Thanks,

Chris

I took on board the comments about formatting the cells, and then pasting special values, however is there a way to run the sheet and have the totals sum from Rows AA:BJ without doing this?

Eventually I would like this to flexible to accommodate different values and sums as this S/S will be used by other people!

Thank you once again,

Chris

Hi Joseph,Ron,

Please ignore my last post - I have managed to sort it!

Thanks,

Chris