i have a setup where there is a location in one column, and a number of instances in another column. corresponding columns places the number of instances in each month thereafter. i am trying to hlookup to another page to find the location and multiply a rate on the other page times the number of instances in a month, by doing this:
=IF($C7>0,HLOOKUP($A7,Rates!$D$28:$AL$78,36,FALSE)*G7,0)
where C7 is the number of instances, A7 is the location, and G7 is the particular month.
the problem is that the column with location can select multiple locations in one worksheet. the number of instances can change in any given location. so there could be locations with instances in C8, D8, E8, F8, etc. this continues for quite some time. i know i could make the formula add each line to itself OR make each addition a separate line, but i'd like one line to show all of the associated numbers and i'm hoping surely there is a sumproduct/sumif statement would do this for me otherwise. any help?
this is what i mean by adding each statement individually:
=IF($C7>0,HLOOKUP($A7,Rates!$D$28:$AL$78,36,FALSE)*G7,0)+IF($C8>0,HLOOKUP($A8,Rates!$D$28:$AL$78,36,FALSE)*G8,0)
=IF($C7>0,HLOOKUP($A7,Rates!$D$28:$AL$78,36,FALSE)*G7,0)
where C7 is the number of instances, A7 is the location, and G7 is the particular month.
the problem is that the column with location can select multiple locations in one worksheet. the number of instances can change in any given location. so there could be locations with instances in C8, D8, E8, F8, etc. this continues for quite some time. i know i could make the formula add each line to itself OR make each addition a separate line, but i'd like one line to show all of the associated numbers and i'm hoping surely there is a sumproduct/sumif statement would do this for me otherwise. any help?
this is what i mean by adding each statement individually:
=IF($C7>0,HLOOKUP($A7,Rates!$D$28:$AL$78,36,FALSE)*G7,0)+IF($C8>0,HLOOKUP($A8,Rates!$D$28:$AL$78,36,FALSE)*G8,0)
