Formula IF exist in Pivot Table field and in a Named Range

**QcSylvanio** · 04-16-2013, 07:37 AM

Hi,

I'm looking to do something here with a Pivot Table and a Named Range. I have multiple Pivot Table setup.

Let say I have a field in my Pivot Table that can be A, B, C, D and E that return value from 1 to 100.

I would like to Sum the value if the Pivot Table field name is include in the named range X.

I attached an exemple.

I really appreciate any help on this.

Thanks !

**dilipandey** · 04-16-2013, 08:06 AM

Do you want when the pivot field name is included in named ranges Project 1/2/3, then only pivot should consider those fields ?
Or I am missing something here...

Regards,
DILIPandey
<click on below * if this helps>

**QcSylvanio** · 04-16-2013, 09:47 AM

Well, actually I need to do 2 things.

1. Formula outside the Pivot Table refering to the Pivot. Something like : sumif pivot field name is included in named range X, then sum pivot table calculated field.

2. (That was not the original post). VBA script that change all pivot table (that names start with Project1) filter to equal Named Range X.

English isnt my first language. I'm sorry about error I may do.

Is this clear?

**dilipandey** · 04-16-2013, 09:59 AM

Not sure.. but if a pivot considers a column then it will show sum for all the present values in that column, unless you check them out using pivot filter

Alternatively, you can edit your pivot data source itself to exclude those items which you don't want to consider .. hope I am on right track.

Regards,
DILIPandey
<click on below * if this helps>

**QcSylvanio** · 04-16-2013, 10:12 AM

No.

I do not want to make any change on my Pivot Table.

I want a formula to check in the Pivot Table for Project that are included in Named Range. For each Project found, sum the value of this project.

**dilipandey** · 04-16-2013, 02:12 PM

Okay.. try below formula as I shown in green cells in attached pivot:-

Formula:

Please Login or Register  to view this content.

Attachment 228527

Regards,
DILIPandey
<click on below * if this helps>

**QcSylvanio** · 04-17-2013, 06:37 AM

Invalid attachment specified.

Can you try to upload it again please.

Thank you !

**dilipandey** · 04-17-2013, 06:43 AM

Okay.. trying to attach once again :-

Test.xlsx

Regards,
DILIPandey
<click on below * if this helps>

**QcSylvanio** · 04-17-2013, 10:34 AM

Hi Sir,

This is great. Thank you.

Now is there a way to do this using Named Range? Because I got around 70 items in some project so doing it this way would give me a really long formula.

So I would like to replace L12 and L13 by PROJECT_1 (Named Range).

This is where Im getting trouble.

Thank you again for your help.

**dilipandey** · 04-17-2013, 03:21 PM

So I would like to replace L12 and L13 by PROJECT_1 (Named Range).

but I did not used them in the formula and if you meant i12 and i13, then I used them separately in formula and hence two names would be required.
See the below formula where I demonstrated this with defined name as data1 and data2 :-

Formula:

Please Login or Register  to view this content.

see attached:- Test (9).xlsx

Regards,
DILIPandey
<click on below * if this helps>

**QcSylvanio** · 04-17-2013, 05:12 PM

Ok.

But doing it this way is the same with or without names.

I have +/- 70 data. I can't do them one by one.

Is there a way to check if $E$*:$E$12 exist in NAMES, then sum the data.

Regars,

**dilipandey** · 04-18-2013, 02:48 AM

Is there a way to check if $E$*:$E$12 exist in NAMES, then sum the data.

and those NAMES will be first defined by you only

and then you can check if $E$*:$E$12 exists or not .. hence effort is there, either way.

BTW, did you noticed the defined name which I used ?
Press Ctrl + F3 to see the name and the logic as that one is dynamic name and you do not need to define them for every new range

Regards,
DILIPandey
<click on below * if this helps>

**QcSylvanio** · 04-18-2013, 06:40 AM

It doesnt work.

I uploader the test file. Check out the NAMES and how I would like to do it !

Thank you again SIR.

**dilipandey** · 04-18-2013, 07:39 AM

As I said

BTW, did you noticed the defined name which I used ?
Press Ctrl + F3 to see the name and the logic as that one is dynamic name and you do not need to define them for every new range

check my attachment which I uploaded in post#10.

Regards,
DILIPandey
<click on below * if this helps>

**QcSylvanio** · 04-18-2013, 02:46 PM

Hi,

I understand the Named Range you create.

But if the Named Range is extended to 2 or more data, it doesn't work.

In this exemple, I have 70 Data to look for.

I dont want to do this 70 times : ($E$8:$E$81=data1)

I may be wrong, but I dont think this is working with your exemple.

Thanks again, SIR.

Formula IF exist in Pivot Table field and in a Named Range

LinkBack

Thread Tools

Rate This Thread

Display

Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Re: Formula IF exist in Pivot Table field and in a Named Range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions