Dynamic Formula Extract Data for multiple criteria

[Karroog]

Basing my formula via this video:

http://www.youtube.com/watch?v=NbP-dHau9-I

Pretty powerful tool. I have setup the initial SUMPRODUCT, with some help from this forum.

I am setting up a translation exists formula and am running into issues. I am SUMPRODUCT to get the overall count that follows the two breakdown areas:

1) no translations but installation exists
2) translations exist but no installations exist

The formula I have created to make this dynamic, as items change the list will change, is below:

FORMULA #1 for No Translations, installations exist

Please Login or Register  to view this content.

FORMULA #2 for Translations, no installations exist

Please Login or Register  to view this content.

Right now the formula is pushing back a #REF, and I am unsure why. It might be because part of this formula is trying to pull in information that is not a number but text. However, as you can see the second formula shows blanks for the "11" items that do exist, but then the REF appears in the 11th line, so I know I am close on this one.

Any help working through this would be helpful. thanks!

[misrasomendra]

Hi,

This part of your formula is wrong INDIRECT($A$9).

You should put the range as an array in INDEX function from where you want the result. Secondly I doubt this part also ROWS(A$10:A10)=$D$4 and ROWS(A$10:A10)=$D$3. Do you want the oput put in that row only or on all the rows less than the numbers in D4 &D3?

[Karroog]

Hi,

This part of your formula is wrong INDIRECT($A$9).

You should put the range as an array in INDEX function from where you want the result. Secondly I doubt this part also ROWS(A$10:A10)=$D$4 and ROWS(A$10:A10)=$D$3. Do you want the oput put in that row only or on all the rows less than the numbers in D4 &D3?

The Rows item area is for when I expand the list to incorporate every single instance counted in from the SUMPRODUCT Function. The Rows expand from A10 to A whatever depending on how many I am trying to populate.

Since D3 = 1, only 1 row will be populated based upon the translation and base hurdle.

Same for D4, it will expand to 11 rows based upon the SUMPRODUCT hurdle.

Would this ROW section be causing the REF#?

I am a bit confused on the indirect assessment, anyway you could explain in more detail would be helpful.

[misrasomendra]

INDIRECT is causing REF# error. Can you upload a sample file, without any formula just with manual output.

Regards,

[Karroog]

the first post contains the original formula and the manual input data.

[misrasomendra]

No please put values you expect in the cells where you are putting the formula. Remove formulas and put values in the cells where you have formula right now.

[FDibbins]

This part of your formula ...
INDEX(INDIRECT($A$9)
is looking for (I presume) a range name that is to be found in A9?

A9 contains "PRODUCT #1" but no such product name exists. The 1st part of INDEX needs to be a range or array.

Im not really sure what you are trying to do there, but it looks like you are trying to pull in languages based on something? Perhaps if you provided a few sample answers, it would be easier to offer suggestions?

Also, Im not sure that it really matters, but it might make things simpler to understand (in the formulas) if your range names were not so long

[Karroog]

Attached is a "manually entered" with no formula of what I believe it should produce once this is all set.

Any help would be great. Thank you.

[misrasomendra]

See the file.

[Karroog]

See the file.

Hi there. This works great!!

My first concern though is tied to the "PRODUCT" area. I missed this in the initial example I sent out, but there will be a potential for multiple different products within the Sheet1 area as I'm going to build it as a roll-up for different product lines. I feel as if the Sheet2 will not focus on this enough, which is why I had the longer formula for that column within my original example.

Attached for you to review.

[Karroog]

Hi there. This works great!!

My first concern though is tied to the "PRODUCT" area. I missed this in the initial example I sent out, but there will be a potential for multiple different products within the Sheet1 area as I'm going to build it as a roll-up for different product lines. I feel as if the Sheet2 will not focus on this enough, which is why I had the longer formula for that column within my original example.

Attached for you to review.

NEVERMIND, I got it. thanks again!