SUMIF w/ changing column refernce ... use an array?

[Ray]

Hello -

I've created a sheet that allows users to see data for a specific
month. They choose the month from a drop-down in cell A3 -- an INDEX
function converts this into a column reference on the Data sheet. The
Data sheet is structured like this:
Col A: Store #
Col B: Account #
Col C: Store & Account (concat'd)
Col D-R: data by month, Total (and a couple of other sums)

Col C contains a unique Store/Acct combination, so I'm able to use
this formula (on the report page) to get the correct figures:
=IF(ISERROR(VLOOKUP(Z$7&$A8,'Store Data_TY'!$A$2:$R$5000,$C$3,FALSE)),
0,VLOOKUP(Z$7&$A8,'Store Data_TY'!$A$2:$R$5000,$C$3,FALSE))
where:
Z7: store #
A8: acct #
C3: column reference

This all works fine -- my issue is that there is one scenario where I
need to SUM the values for 2 stores! I need to be able to see them
separately, but most of the time they should be added. If not for the
changing column reference, I could use SUMIF ... but how would I build
it to incorporate the changing column reference?

I think my answer lies with an ARRAY formula, but these things always
confuse me ....

Any ideas?

TIA,
ray

 

[Tim879]

You can use the offset function to select the correct column in the
sumif.

For example (and this is an admittedly bad example), using your data
structure.
Col A: Store #

Col B: Account #
Col C: Store & Account (concat'd)
Col D-R: data by month, Total (and a couple of other sums)

The following formula would work if A1 is your criteria and B1 is the
month you want to look up.
=SUMIF($C$2:$C$6,A1,OFFSET($C$2,0,MONTH(B1)))

The reason it's a bad example is if the date if blank, it
automatically returns the first column of data. Furthermore, there's
not a 13th month so it would only give you the ability to work with 12
months of data.

That being said, depending on the logic in your spreadsheet, you could
also use the MATCH function in place of the MONTH function to find the
column you need.

 

[Bob Phillips]

How would you identify the two stores, and why a changing column reference?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ray]

Hi Bob -

Stores are identified by a 3-digit number ... in this case, 159 & 161
are the ones that would need to be combined (along with the account
number) -- for instance, for acct#3011, it would be 1593011 + 1613011.

The 'changing column reference' is needed b/c the user selects the
month of data they want to see. The data dump (from another system)
comes out structured like this:
Col A: Store #
Col B: Acct #
Col C: [blank, per my instruction to SysAdmin]
Col D: September data
Col E: October data
etc.....

Col D is September to coincide with Fiscal Year timing. So, if the
user selects "September" from the drop-down, the column reference
(cell C3 from example above) would equal 4.

So, to summarize, if the user wants to see (for ex) December, the
formula would look like this (in layman terms):
Sum the values in the 4th column (from left) where the value in Col D
equals either 1593011 OR 1613011

Does that clarify the situation? or, is there a better way to do
this?

 

[Bob Phillips]

Assuming the month is in M1, then

=SUMPRODUCT((ISNUMBER(MATCH(A2:A20,{1593011,1613011},0)))*(D2:G20)*(ISNUMBER(MATCH(D1:G1,M1,0))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ray]

Thanks alot Bob ... I'll check it out and come back if any further
problems ...