In Excel, I have found that an index column that is formula driven and based off of a date cell in a cell immediately to the right of the derived formula [ eg: =TEXT(MONTH(A151),"0#")&"-"&TEXT(DAY(A151),"0#") ] somehow causes the value to be added a second time when using sumif. I moved the column to the left and all was well with the formula. Why? I have not a clue. ???

I'm having trouble figuring out what you are describing. Maybe I'm just slow on this Monday morning. ;-) Could you please provide some actual values and formulas so that I can set up a sheet and test the "problem"?

Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Another question, why are you using this formula: =TEXT(MONTH(A151),"0#")&"-"&TEXT(DAY(A151),"0#")

When this formula does the same thing with less confusion:

=TEXT($A$151,"mm")&"-"&TEXT($A$151,"dd")

You end up with a TEXT string of the Month & Day with both formulas?

MIKE

Mike, It may be a throwback to my dinosaur age. Or, maybe an extra round of tequila the day before?!?! But... It was a method that I selected to pad out the month and day with a "0". Cleaner is always better. Easier to audit, as well. Good advice, thanks.

More important than that, I was trying to figure out why my Excel was considering the source in the original date if it was found to the right of my first column in a named range when used with a "sumif" function. I know it did not work, as I witnessed the double value, but, was wondering if anyone had also experienced that hiccup, and if there was a correction to the value error kicked out.

The source spreadsheet was extracted (exported) into Excel from a google docs spreadsheet entry. ...If that matters in this quest for understanding.

Yours in spreadsheets,

Tequila George

As DerbyDad03 asked in his message:

Could you please provide some actual values and formulas so that I can set up a sheet and test the "problem"?Your question:

I was trying to figure out why my Excel was considering the source in the original date if it was found to the right of my first column in a named range when used with a "sumif" function.Tough to visualize what's going on without at least the SUMIF() function,

and an example of your data.Please read this How-To, which explains the use of the < PRE > tags to align your data and post a small sample.

And using Column Letters and Row numbers is always helpful.http://www.computing.net/howtos/sho...

MIKE

re: " More important than that, I was trying to figure out why my Excel was considering the source in the original date if it was found to the right of my first column in a named range when used with a "sumif" function."Please keep in mind that we can not see your worksheet from where we are sitting.

Unless you provide some more detail, such as example data and the SUMIF formula

that is giving you trouble, there is not much help we can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think third-party cookies should be blocked by browsers?

Discuss in The Lounge

Poll History