SUMIF from NAMES using CONCATENATE

Kilowoo

New Member
Joined
Nov 27, 2009
Messages
2
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.
moz-screenshot-2.png
moz-screenshot-3.png
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top