LinkBack URL
About LinkBacks
Hello,
I Just want to average Column "Q:Q" for all Column J:J contains "VANILLA VOIP" and "VoIP Enterprise"
I've use the below formula but return nothing.
Please Login or Register to view this content.
Hello,
I Just want to average Column "Q:Q" for all Column J:J contains "VANILLA VOIP" and "VoIP Enterprise"
I've use the below formula but return nothing.
Please Login or Register to view this content.
I think you're going to have to do
=SUM(SUMIF(J:J,{"VANILLA VOIP","VoIP Enterprise"},Q:Q))/SUM(COUNTIF(J:J,{"VANILLA VOIP","VoIP Enterprise"}))
Try
Formula:
Please Login or Register to view this content.
Hi zbor, I've got an error "you've entered too many arguments for this function...."
Try removing the last argument/range from teh countif()...
=(SUMIF(J:J,"VANILLA VOIP",Q:Q)+SUMIF(J:J,"VoIP Enterprise", Q:Q))/(COUNTIF(J:J,"VANILLA VOIP")+COUNTIF(J:J,"VoIP Enterprise"))
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
Thanks Ford.
But when I try to incorporate it to vba, it gives me this formula:
wherein my code is:Please Login or Register to view this content.
Please Login or Register to view this content.
Use cells to hold the criteria:
A1 = VANILLA VOIP
A2 = VoIP Enterprise
Then, this array formula**:
=AVERAGE(IF(ISNUMBER(MATCH(J2:J100,A1:A2,0)),Q2:Q100))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
You should avoid using entire columns as range references in array formulas. Use smaller specific ranges.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Remove the R1C1
And did you try the formula I posted? It does essentially the same thing..Originally Posted by siobeh
Thanks Ford.
But when I try to incorporate it to vba, it gives me this formula:Please Login or Register to view this content.
SUM(SUMIF(J:J,{"VANILLA VOIP","VoIP Enterprise"},Q:Q)) is a nice shortcut for SUMIF(J:J,"VANILLA VOIP",Q:Q)+SUMIF(J:J,"VoIP Enterprise", Q:Q)
Perhaps you should have mentioned that from the start?But when I try to incorporate it to vba, it gives me this formula:
Agree with Jonmo, I was just resolving the formula offered by zbor
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks