SUMIF from NAMES using CONCATENATE

[Kilowoo]

Hi All!

I'm using Excel 2003, and Windows XP, anyway i think this might be an easy question.

I have a good file where I pulled some figures ranged in names based on a concatenate data on other tab. I've used "names" for the different ranges of data.

The cells c$2 to C$4 are "criterias" used to pull the data I want (e.g. LATAMActual12010 = Geography,Actual,Period,Year)

The formula is:

=+SUMIF(DetailInfo;CONCATENATE($C$2;$B$9;$C$3;$C$4;Revenue)

For example, I have the DetailInfo range, that included the concatenation on the first column, and all the other data columns to the right. Inside it I have the Revenue name, that included the revenue column data.

http://www.humyo.com/F/1265145-2122335203

The problem is I've added a column in the DetailInfo field of the name, and now the formula that was working OK is bringing me incorrect numbers. Much higher, not sure what it changed. The name remains the same, but the values the sumif is pulling based on exactly the same concatenation are wrong.

The formula is exactly the same, and the names are correctly defined.

I've tried deleting the name, and creating it again, but the problem still persist.

If I go back to a previous version of the book, the formula still works fine.

Any help is much appreciated!

Thanks a lot!
Regards,

K

PS: I've search the Q&A but couldnt find this specific issue.

 

[Aladin Akyurek]

Care to post the formula which creates DetailInfo?

 

[Kilowoo]

Hi All!

I'm done, you can close the thread.

The issue was that as while creating a "quarterly" criteria, I was repeating one of the supposed uniques strings, so in this way, adding more data than it should. I have changed the number for quarters (1,2,3 & 4) to Q1, Q2, Q3, & Q4 in order to avoid the string confuse month periods with quarter periods.

Thanks all!

Regards,

K

PS: DetailInfo was the total range of columns with data, starting on the very left column where the Period concatanation has place.

 

[Aladin Akyurek]

Hi All!

I'm done, you can close the thread.

The issue was that as while creating a "quarterly" criteria, I was repeating one of the supposed uniques strings, so in this way, adding more data than it should. I have changed the number for quarters (1,2,3 & 4) to Q1, Q2, Q3, & Q4 in order to avoid the string confuse month periods with quarter periods.

Thanks all!

Regards,

K

PS: DetailInfo was the total range of columns with data, starting on the very left column where the Period concatanation has place.

Try to concatenate like this...

=A2&"|"&B2&"|"&C2

The pattern must be also used in constructing a criteria for SumIf...