SUMPRODUCT as subtotal

[Guest]

Is there any way to get the behavior of SUBTOTAL (i.e. ignoring other SUBTOTALS, etc.) with SUMPRODUCT or some equivalent, maybe an array formula?

Thanks,

 

[Frank Kabel]

Hi
could you please provide some more detail what you're trying to do
(e.g. give some examples)?

 

[Myrna Larson]

Hopefully somebody will prove me wrong, but I think this would required VBA to
examine the contents of the cells, looking for subtotal, etc, before including
them.

Is there any way to get the behavior of SUBTOTAL (i.e. ignoring other

SUBTOTALS, etc.) with SUMPRODUCT or some equivalent, maybe an array formula?

 

[Guest]

The following simple example would use this mysterious SUBTOTALPRODUCT function to come up with the number for Planning expenses, per person expenses, and total cost.

Does this make sense?

Cost Amount #
------------------------------------------------------
Planning Expenses 3,700 1
Air Fare 1,000 1
Additional Transportation 500 1
Accommodations 700 3
Phone Bills 100 1
Contingency/Misc. 3,125 1
Ground Transportation 10,000 1
Per Person Expenses 1990 1
Round Trip Airfare 1,000 1
Visa/Entance Fees 140 1
Meals & Accommodations 350 1
Excursions 250 2
_________________________________________
Total Cost 18,815

 

[cameron]

It's not necessary. SUMPRODUCT will do just fine.

Cameron Huang