Shorten a formula

[AjayGunware]

hi folks, will it be possible please to help in shortening or suggesting an alternative formula so that it can account for at least 100 rows for the below calculation of Burgers per Person ?

City	Burgers	People
A	10	5
B		10
C	20	4
D	30	6
Total	60	15
Ratio		4

<tbody>
</tbody><colgroup><col span="3"></colgroup>

To calculate Total people, I have used below formula as I need to consider only those people where a number is present under Burger column.

=SUM(IF(ISNUMBER(B2),C2,"0"),IF(ISNUMBER(B3),C3,"0"),IF(ISNUMBER(B4),C4,"0"),IF(ISNUMBER(B5),C5,"0"))

I feel it is easy for just 4 rows but this might become a tedious formula for 100 rows ! please help....

Regards,
Ajay Gunware

 

[CsJHUN]

Hi, welcome a-board!

Did you tried/thinked/have experience with pivots?

 

[arthurbr]

Alternatively = sumif(b2:b100,">0",c2:c100)
adapt your range as needed

 

[AlanY]

extend the ranges to suit

=SUMIF(B2:B5,">0",C2:C5)

 

[Tetra201]

ADVERTISEMENT

Would such entries be valid:
Burgers...0 People...10
Burgers...1 People...10?

 

[AjayGunware]

Awesome.. thank you for the solution

 

[AjayGunware]

Hi Tetra201, I had replaced real parameters with dummy values for the sake of putting example and for ensuring clarity in my question. thank you