Calculate based on two columns

**ohitefin** · 03-20-2012, 04:46 PM

Hi,

I am trying to use column A and Column B in the following example file and based on those two calculate on Column C

So for example, I do a VAR calculation for Location IDs 19 which fall in Store # xxxx.

I tried to use a pivot table, but I need to then perform calculations based on the var calc and pivot tables don't provide that functionality ( I could be totally wrong). So for example, if the variance on Location ID 19 within store # xxxx is 0, then I know it's consistent and I want to say that Store # xxxx is consistent, or inconsistent.

If any of this doesn't make sense please forgive me, and I will clarify.

Thanks in advance for your help.

Excel Example for Question.xlsx

**Dennis7849** · 03-20-2012, 06:09 PM

I do not understand what you are trying to do.

**Acem13** · 03-20-2012, 06:24 PM

What is it that you are trying to calculate? I'm really confused by what you are trying to do.

**ohitefin** · 03-20-2012, 07:32 PM

Any calculations other then sumif really. Basically if store # is x and location id is y, var on all column c items that fit into this categpry

**daddylonglegs** · 03-20-2012, 07:48 PM

Perhaps try this array formula in D2

=VAR(IF(A$2:A$28=A2,IF(B$2:B$28=B2,C$2:C$28)))

confirmed with CTRL+SHIFT+ENTER and copied down

...or this variation will only give you a result on the first instance of each Store/Location combination

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,VAR(IF(A$2:A$28=A2,IF(B$2:B$28=B2,C$2:C$28))),"")

**ohitefin** · 03-25-2012, 10:27 AM

Awesome, thank you for your help daddylonglegs, but how do I change the result so that instead of a var over the entire column on the first instance of each store/location combo, I only get the var for the part of the column that the store/location combo applies to?

Thanks.