SUM all rows IF a cell is part of a named range?

[pupmup]

Hi all,

I've been playing around with CSE formulae.

I can work out the weighted average for all entries in a large table (Company, Vol Sales, Val Sales) where the company for an entry is X:

In the below,

{=SUM(IF(A$1$:$A$1000="Company",$D$1:$D$1000,)/SUM(IF($A$1:$A$1000="Company",$E$1:$E$1000,)}

and that works well.

(In the actual formula "Company" points to a cell with the company name in it, so I can just drag down to scale up the results).

I have say four top companies, and 14 smaller ones. I want to aggregate the other fourteen companies into one calculation. How do I say IF A1:A1000 = (any of the names listed in cells H3:H17)...etc? I've turned H3:H17 (the list of companies) into a named range of OtherCompanies, if it helps.

Thank you! Your help appreciated.

Sam

 

[bewsh1987]

why cant you use just =sum(H3:H17) seem like a simpler method

however if you have a range and you want to sum a range with only values in then use
=sumif(A$1$:$A$1000,"OtherCompanies",$E$1:$E$1000)

Also =sumif(range,criteria,[sum_range]) is a better method less errors than arrays

 

[XOR LX]

Hi.

The standard way would be to include a construction such as:

IF(ISNUMBER(MATCH(A1:A1000,OtherCompanies,0)),...

Regards

 

[bewsh1987]

deleted

 

[pupmup]

Thank you for the replies. I was massively overcomplicating things.

My eventual solution needed to filter both by company and by location (found in H33 and I31 below).

{=SUM(IF(($C$6:$C$327=$H33)*($D$6:$D$327=I$31),$F$6:$F$327,)/SUM(IF(($C$6:$C$327=$H33)*($D$6:$D$327=I$31),$E$6:$E$327,)))}

But I did as suggested above and changed it to SUMIFS

=SUMIFS($F$6:$F$327,$C$6:$C$327,$H43,$D$6:$D$327,I$41)/SUMIFS($E$6:$E$327,$C$6:$C$327,$H43,$D$6:$D$327,I$41)

That works fine for everything but the named range.

For that I just replaced the company cell reference with the named range and CSE. For some reason it wouldn't work as a regular formula.

{=SUMIFS($F$6:$F$327,$C$6:$C$327,OtherStudios,$D$6:$D$327,I$41)/SUMIFS($E$6:$E$327,$C$6:$C$327,OtherStudios,$D$6:$D$327,I$41)}

Thanks again all xx

 

[pgc01]

I'm not sure I understand.

You mean OtherStudios is a named range referring to a contiguous range of cells with names?

 

[pupmup]

Precisely.

So cells I3:I14 or so have a studio name each in them.

 

[pgc01]

Then I don't see how your formula can work. YOu should sum the results of the SumIfs(). Did you test with more than 1 name in the list?

 

[XOR LX]

Then I don't see how your formula can work. YOu should sum the results of the SumIfs(). Did you test with more than 1 name in the list?

Was about to post the precise same comment!