Regular formula or array for multi colum multi criteria?

[scaffdog845]

Hey gang

In the attached spreadsheet I have an example of a time entry setup in A7:F15. The source workbook goes from A7:F66. I'm looking for a formula that would give me a sum of the charges entered for the day. It's easy enough to use a helper column and sum it up like I have in the example in H7:H16 but I was hoping for a formula or array formula that could be put at the bottom of F:F to calculate the totals without the helper.

Thanks

[AlKey]

If you just need a total

Please Login or Register  to view this content.

[Jonmo1]

Try

=SUMPRODUCT(F7:F15,C7:C15)

[JohnTopley]

Try in F16

=SUMPRODUCT(($C$7:$C$15)*($F$7:$F$15))

[Tony Valko]

I won't be repeating the same formula.

On a side note...

This was the original intended use for SUMPRODUCT. My, how its use has morphed over the years!

[scaffdog845]

Must be a Friday because I was over thinking that one to the extreme! Thanks guys

[Jonmo1]

You're welcome.

FYI, the difference between the 2 methods is subtle, but significant.

=SUMPRODUCT(C7:C15*F7:F15)
=SUMPRODUCT(C7:C15,F7:F15)

With the (C7:C15*F7:F15) syntax, sumproduct isn't actually doing the product part (C7*F7 and C8*F8 etc)
Instead, that is being done internally, and the results of each product are fed to sumproduct as an array, like
=SUMPRODUCT({200,188,423,etc})
Then the only thing sumproduct is actually doing is the SUM part.


With the (C7:C15,F7:F15) syntax, sumproduct is actually doing ALL the work.


This isn't really a big deal, and I don't know if there is any performance impact.
However, the first method (C7:C15*F7:F15) would result in #Value! Error if there are any TEXT values within either range.
But the 2nd method (C7:C15,F7:F15) would simply ignore those text entries and continue with the rest of the calculation.