Here is the break-down:
Criteria (text) in A:A and criteria (text) in B:B needs C:C to contain at least all text provided by A:A and B:B. I then need to average all of the cells in C:C which met the criteria aforementioned by using the values provided in D:D.
Please advise.
Thanks!
with averageifs
it works slightly differently to how i think you need it to work
AverageIFS ( the range to average , criteria_range1, criteria1 , criteria_range2, criteria2 ,)
A:A has text and then the criteria looks for that in A:A
so if you want the text "FRED" then "FRED" will need to exist in column A
and for 2nd criteria
say the number 4
Then 4 will be looked for in the next range B:B
so
averageifs( D:D. A:A, "FRED", B:B, 4)
so not sure what you mean by
can you give a few examples of what is in each column and what you want to use as criterianeeds C:C to contain at least all text provided by A:A and B:B.
or better still, attach a spreadsheet here with examples
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Okay, attachment has been provided.
Basically column C is the reference range for columns A and B. Let's take A2 and B2. I need to find all of the cells in C:C that contain A:2 and B:2 (in any order) and then average them according to the prices found in D:D (please disregard E:E, F:F). Please also take note that there may be missing cells in D:D, and should not be treated as a 0 because that will throw off the average. Basically negate and blank fields in D:D.
Thanks for you help!
-Fat Al
I think this is something that needs to be done with PivotTable not with formulas.
Please see attached file.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Do you want to get the average of items that contain BOTH A and B? Maybe provide a few sample answers so we can see what you want?
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
I don't think a pivot table is what I'm looking for.
Let me try to explain further:
The 'brand' in cell A2 can have the following 'types' which are stated in Column B (Booties, Boots, Flats, Flip Flops, Mules, Pumps, etc. etc.).
If one cell in C:C meets the brand and type combination, it will then provide the 'retail price' found in D:D.
If there are 2+ cells in C:C that match the criteria, it will take all cells that are TRUE and average them together.
Here is an example of a possible answer:
adidas (A2) + sneakers (B10) => C586 (64.99), C783 (79.99), C906 (69.99), C1444 (69.99), C1798 (64.99) = 69.99 avg.
I also updated the attachment to accommodate for the calculated averages. Please see H:H --> R:R (example above marked).
Thanks!
Some cells of D:D contain #VALUE. Try to fix them then apply this formula in H2:
Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Quang PT
Just notice your file has been updated. Try to fix cells with errors in D column, then try in I2:
Array formula.Please Login or Register to view this content.
Drag down and across.
Due to big data, PC may run slowly. Try with VBA solution anyway.
Thanks bebo, no hiccups here!
Cheers,
Alan
Rep added to all on thy post!
Thank you friends!
nice to hear it works! please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Happy the 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