LinkBack URL
About LinkBacks
Hi,
Can onyone help me on how to create AVERAGEIFS with multiple criteria in the same criteria range
e.g. January, February, March, etc.
Please Login or Register to view this content.
Hi,
Can onyone help me on how to create AVERAGEIFS with multiple criteria in the same criteria range
e.g. January, February, March, etc.
Please Login or Register to view this content.
Hi,
I have tried this code but still not working. Please help me
Please Login or Register to view this content.
Hi,
Anyone please help me on this. I have tried many times but still got error.
Thanks.
Farid
Hi Farid,
I thought you said you had an answer back in:
http://www.excelforum.com/excel-form...t-working.html
What was wrong with my answer back in that thread?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
HI marvinP,
I have already replied that your formula works but what i want still not working
AVERAGE(IF((H515:IW572=H503)*(H519:IW576={"January","February"},H524:IW581))) CRTL + SHIFT + ENTER
I have also have tried the VBA code as above, do you know what went wrong on the #2 post ? please help. Many thanks for willing to assist.
Hi,
Anybody who are expert, please assist me.
I have tried many methods, but still not working.
Regards,
Farid.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Hi xladept,
Attached herewith the sample of workbook in a small range. Here is VBA code which has error on 2nd the calculation
In the meantime, only one of my Excel formula work & another one notPlease Login or Register to view this content.
This one working fine
Where, M4 = January, M5 = February, M6 = March
=AVERAGE(IF((H17:Z17=H5)*((H21:Z21=M4)+(H21:Z21=M5)+(H21:Z21=M6)),H26:Z26)) CRTL + SHIFT + ENTER
The following not working
=AVERAGE(IF((H17:Z17=H5)*((H21:Z21={"January","February","March"})),H26:Z26)) CRTL + SHIFT + ENTER
Please help me, Thanks a lot
Regards
Farid
Hi Xladept,
Have you open the attachment to look into this matter?
If can't, kindly let me know, I will attach it again. Thanks
Regards,
Farid
Hi Farid,
I'm not good at the array formulas, I've asked the other experts for help
Hi Xladept,
Thanks for reply.
Hi All,
Can you help me too.
Regards,
Farid
Error Number 1
Averageifs() don't accept multiple Column range as criteria range in its arguments
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Here is the working code
Please Login or Register to view this content.
Hi Sixthsense,
Thanks for willing to assist me.
The result should be 3.53 instead of 0.
Please refer to my attachment.
Regards,
Farid
Because in that range there is no matches found and due to this averageifs() will throw #DIV/0! error.
I used Iferror() to handle the errors, so whenever averageifs() arrives #DIV/0! error then it will convert the error as 0.
below is the formula which can be used in excel cell for testing...
=SUM(AVERAGEIFS(H524:H581,I515:I572,"Less Complex",J519:J576,{"January","February","March"}))
Hi Sixthsense,
Based on the attachment, my problem is to calculate average in multiple column. If averageifs unable to accept multiple Column range as criteria range, what is alternative method to count average of multiple column in VBA?
As for the excel fomula I found this, but there is no special VBA code (Application.WorksheetFunction.XXXX) to run in VBA.
=AVERAGE(IF((H17:Z17=H5)*((H21:Z21=M4)+(H21:Z21=M5)+(H21:Z21=M6)),H26:Z26)) CRTL + SHIFT + ENTER
Where, M4 = January, M5 = February, M6 = March
Regards,
Farid
Why not simply try like this?
Please Login or Register to view this content.
Hi Sixthsense,
It has return 0 value.
When I put FormulaArray, it has returned false.
Please Login or Register to view this content.
If the below formula works in a excel cell (Should be entered as array) then my suggested code in post #17 should also work.
=AVERAGE(IF((H17:Z17=H5)*((H21:Z21=M4)+(H21:Z21=M5)+(H21:Z21=M6)),H26:Z26))
Hi Sixthsense,
Thanks a lot. Both formula works now.
I want to paste it in a cell. So, this is what I want. It is much better compared to record macro.
Please Login or Register to view this content.
However I'm still felt slightly disappointed due to no special VBA code (Application.WorksheetFunction...) to resolve this problem.
Regards,
Farid.
Last edited by Faridwahidi; 05-24-2014 at 12:36 AM.
Still I am unable to understand what is your problem in using the Array Formula in VBA Evaluate() function
Enter the below formula in a excel cell as Array
The result will be 55 when you enter it as array.
=SUM(ROW(A1:A10))
Try this code in VBA and see the result
Please Login or Register to view this content.
Hi,
Finally, It works.
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks