• 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.

Index match

S

shadedlight

Member
Hello,

I've created an Index match in B2 and C2 as well as a data validation list in A2. Now I want my 'overview' sheet to calculate the monthly total of all paychecks in B5 based on the month chosen in A2.

Ex. If I choose January 29 from the data validation in A2 I want B5 to calculate all the monthly pay checks in January from the 'Pay Dates' worksheet. thanks
 

Attachments

Hi,

How data comes column A, manually?
I see again the difference monthly calculation is 28 days, that OK, the calculation would be 30 days, plus or minus four days?

David
 
Hi David,

A2 on the overview sheet is a data validation list. On the Overview sheet there is an index match formula in B2 and C2. This part works well.

I did an index match formula in B5 where when I enter the month name in A5 it will give me the total for the month. What I want to accomplish is for that to happen automatically. I want my 'overview' sheet to calculate the monthly total of all paychecks in B5 based on the month chosen in A2.

As it is now I have to choose from the data validation list in A2 to populate B2 and C2 to get the Bi weekly paychecks. Then I have to choose from the data validation list in A5 to populate the monthly total in B5. I'm trying to figure out how I can only choose from the data validation list in A2 to populate the monthly total in B5.
 

Attachments

To calculate the monthly total of all paychecks in B5 based on the month chosen in A2.

In B5, enter formula : (base on the OP's post #.1 Tables)

=SUMPRODUCT((MONTH('Pay Dates'!A$2:A$27)=MONTH(A2))*'Pay Dates'!B$2:C$27)

Regards
Bosco
 
Bosco,

Yes that's exactly what I want. Thank you thank you. How can we make the name of the month that B5 is referencing appear in A5? Thanks again
 
You must log in or register to reply here.
Back
Top