# Regular formula or array for multi colum multi criteria?

1. ## Regular formula or array for multi colum multi criteria?

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

2. ## Re: Regular formula or array for multi colum multi criteria?

If you just need a total

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Regular formula or array for multi colum multi criteria?

Try

=SUMPRODUCT(F7:F15,C7:C15)

4. ## Re: Regular formula or array for multi colum multi criteria?

Try in F16

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

5. ## Re: Regular formula or array for multi colum multi criteria?

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!

6. ## Re: Regular formula or array for multi colum multi criteria?

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

7. ## Re: Regular formula or array for multi colum multi criteria?

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1