sumproduct can this be done?

[turtle726]

Hi,

I have an excel sheet that spawns from a browser, and would like to sum qty's the based on criteria and rows. The problem I face is I do not know what the rows will be when the sheet generates.

Please Login or Register  to view this content.

Is it possible?

[NBVC]

if the style and colour are always grouped together as shown in your sample then you can just use a range that goes to the bottom of the sheet... it will only sum the items where the corresponding style and colour match your criteria.

e.g. In I4, use formula:

Please Login or Register  to view this content.

copied across.

[turtle726]

NBVC, thanks soooo much! awsome! Is there a way to add another criteria in for the size (rows I2:U2)? For your solution below the size would be 0-6M.

Please Login or Register  to view this content.

[NBVC]

I am not sure why you need that since your formulas are directly under each of those column headers and they can be copied across (the last range I$5:I$65535 would adjust to J$5:J$65535, and so on).

But if you must.... then we will need to revert to an array entered Sum(IF()) formula since the I:U range has numbers and text.....

so the new formula in I4 would be something like:

Please Login or Register  to view this content.

which must be confirmed with the CTRL+SHIFT+ENTER keys, not just the ENTER key. You will notice { } brackets appear around the formula.

Then you can copy across.

Edit: Actually that last range in this formula can be expanded to include all columns

Please Login or Register  to view this content.

[turtle726]

Hi NBVC,

Thanks for your help. I understand what you mean when I copied across. For the next set of Styles and Colors though, when I copy across, I get a circular reference error.

[NBVC]

Remove the $ from before each 5 in the formula.

[turtle726]

Remove the $ from before each 5 in the formula.

Thanks again! I really appreciate it.

For some reason though when the sheet spawns from my browser, I will get the circular reference error. But when I copy to each set, it works perfect.

The attachment even gives the error.

[oldchippy]

Hi,

This has got rid of the error

[turtle726]

oldchippy,

Thank you m8. The part I will have trouble with is I don't know what the row numbers will be.

I had it going something like this...

Please Login or Register  to view this content.