i have an excel spread sheet with clients, representative and their first pay date and amount. i would like to create a forumal to look to calculate average payment based off a specific rep and date. For example:
Client Rep Date Payment
Joe B Aaron Feb $400
Joe C Dennis Mar $365
Joe D Eddie Feb $262
I was looking for a formula to to pull average payment by rep and specific month for example, average payment for Eddie for Feb.
Any help would be appreciated.
Assuming your table is in A2:D10, then try
=Average(if(B2:B10="Eddie",If(C2:C10="Feb",D2:D10))
this formula must be confirmed with CTRL+SHIFT+ENTER, not just ENTER. You will see { } brackets appear around the formula.
any adjustments in the formula will require you to re-confirm with CSE keys.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
thank you so much that worked flawlessly, couple other questions, i have column heading for each and when i sort it sorts the heading as well, anyway to stop that? also i would like to be able to count account by rep and month set, i have tried countif without success. any help appreciated.
When you sort, make sure you select "Header Row" in the "My data range has" at the bottom of the Sort dialogue window.i have column heading for each and when i sort it sorts the heading as well, anyway to stop that?
something like (using same assumptions as before):i would like to be able to count account by rep and month set, i have tried countif without success. any help appreciated.
=Sumproduct(--(B2:B10="Eddie"),--(C2:C10="Feb"))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks