Would like a formula to average values if the 2 conditions are met. Average shipping time C2:C20 on Product Sheet if product name in B2:B20 of Product sheet and region in D2:D20 on Product sheet conditions are met.
Example, Alicante, Bouschet (product in D2 of Outcome sheet). Average Alicante, Bouschet if region condition is satisfied. Desired answer = 14. See F4 and F5 for desired outcome.
Sample file attached.
withdrawn........
Last edited by protonLeah; 01-09-2017 at 09:52 PM.
Ben Van Johnson
Or this...
=AVERAGEIFS(Products!$C:$C,Products!$B:$B,"*"&Outcome!D4&"*",Products!$D:$D,Outcome!E4)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
protonLeah: proposed formula is an array and will need to press Ctrl + Alt + Enter. Please clarify.
Although SUMPRODUCT is a function which works with arrays, it does not normally need to be committed using Ctrl-Shift-Enter.
However, the AVERAGEIFS solution put forward by Ford is likely to execute more quickly.
Hope this helps.
Pete
Yes: it is an array formula but needs correcting.
=SUMPRODUCT(--(ISNUMBER(SEARCH(Outcome!D4,Products!B2:B20))*(Products!D2:D20=Outcome!E4)),Products!C2:C20)/SUM(--(ISNUMBER(SEARCH(Outcome!D4,Products!B2:B20))*(Products!D2:D20=Outcome!E4)))
Use Ford's non-array AVERAGEIFS
FDibbins: works like a charm! Thanks FDibbins.
JohnTopley: thanks JohnTopley.
Happy to help and thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks