• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT based on criteria

YPJunk

YPJunk

Member
Hello!

In reference to the attached excel spreadsheet:

What would be the SUMPPRODUCT equation that recognizes if the Rate (column B) should be adjusted to $40 (the max rate)? (So I do not have to break out the data in more columns, i.e. column D)

Thank you for your help!!
 

Attachments

Hello Thank you for your reply.

@Chihiro
=SUMPRODUCT(IF(B2:B38<=40,B2:B38,40)*A2:A38) --> returns $140,815.85 (the correct sum is $130,619.27)

@vletm I need one formula that will result in $130,619.27. (Hours x Rate of $40 or less)
 
Hi @vletm

I would like to have one equation based on columns A and B only. This way I do not have to have column D.

I know what the answer should be, but was wondering if there is an equation I can use that would allow me to save a step.

Thank you.
 
@Eloise T That formula is correct but not the right set up for me. I would like one equation in one cell. I uploaded an updated file for clarity. Thank you.
 
You must log in or register to reply here.
Back
Top