Shorten a formula

AjayGunware

New Member
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

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

CsJHUN

Active Member
Hi, welcome a-board!

Did you tried/thinked/have experience with pivots?

arthurbr

Well-known Member
Alternatively = sumif(b2:b100,">0",c2:c100)

AlanY

Well-known Member
extend the ranges to suit

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

Tetra201

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

AjayGunware

New Member
Awesome.. thank you for the solution

AjayGunware

New Member
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

Replies
17
Views
736
Replies
5
Views
282
Replies
0
Views
191
Replies
3
Views
196
Replies
0
Views
169

1,195,990
Messages
6,012,723
Members
441,722
Latest member
tpaman1975

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back