Combining COUNTIFS with VLOOKUP

Hi Gurus,

I have a dataset that has a location with dates and percentages and i need a formula that would do a vlookup on the location and count the dates and return the result of the percentage based on a range of dates selected. So if I select a date range say from Jan 1st 2017 to Mar 31st 2017, the formula would count the number of dates occurring for that location and return an average of the Diff %.

Hope I am making sense.

I am attaching the data set so that you can get an idea of what I am talking about. If you require further information, please let me know as I might not have been as clear as I should be.

Thanks.

Try

=AVERAGEIFS(C:C,B:B,">=" &E3,B:B,"<=" &F3)

Hi John,

Thanks for your quick response, however, I also need the formula to return the results by location. Can you help with that portion also please?

Thanks.

Add the location criteria to the AVERAGEIFS:

=AVERAGEIFS($C:$C,$B:$B,">=" &$E$3,$B:$B,"<=" &$F$3,$A:$A,$A2)

so this will average for location in A2

put in Column D and copy down

You will get repeat results for a location where all the criteria match

Thanks again John. Will process this and I will have some follow up questions.

Cheers.

Hi John and other Gurus,

I have now added in a MAX and MIN column. I now need two formulas, one for MAX and one for MIN that would give me, for each date within the month, what the MAX % was and what the MIN % was....e.g. for location 10039, there are two dates in March (1 & 15), however I need the formula to return the highest of the MAX, in this case 6.1% and the same for MIN, the lowest, in this case 0.0%.

Any help is appreciated. (Attaching a new excel file)

Hi Everyone,

Just checking in to see if anyone is able to assist me with a formula that would work for me in this scenario.

Thanks.

Jeptik

For MAX

=MAX(IF(($B$2:$B$2000>=$H$3)*($B$2:$B$2000<=$I$3)*($A$2:$A$2000=$A$11),$C$2:$C$2000))

for MIN

=MIN(IF(($B$2:$B$2000>=$H$3)*($B$2:$B$2000<=$I$3)*($A$2:$A$2000=$A$11),$D$2:$D$2000))


$A$11=Location 10039

...both 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.

Thanks John! I will apply the formula and if I have any questions, will circle back.

Cheers!

Hi John,

Once again, thank you for your quick response!

I just applied the formulas and they work like a charm. Can't thank you enough for this as it now makes my work so much simpler and the boss would love me

Hi John and other Gurus,

I have just reopened this thread as my next question pertains to the same file I am currently working on.

Now that I have the date periods set up so that the data changes as I change the date, I would like to introduce another variable into the mix.

I would now like to add a bedroom type into the mix and have the data update as I change the date and bedroom type. I am assuming that a vlookup might have to append on the countifs formula but I could be wrong. So if I have the bedroom types in cell L3 (maybe as a dropdown list), not sure, and I change the bedroom type, the data should update accordingly.

I am attaching as much data as I feel might be required to solve this, however if you require more, please let me know.

As usual, your help is appreciated. This is the last piece of the puzzle that I need solved....I hope.

Thanks.

in I5

=IFERROR(AVERAGEIFS($E:$E,$B:$B,">="&$I$3,$B:$B,"<="&$J$3,$A:$A,$A11,$F:$F,$L$3),"")

in I7

=IFERROR(MAX(IF(($B:$B>=$I$3)*($B:$B<=$J$3)*($A:$A=$A11)*($F:$F=$L$3),$C:$C)),"")

in I8

=MIN(IF(($B:$B>=$I$3)*($B:$B<=$J$3)*($A:$A=$A11)*($F:$F=F3),$D:$D))

... I7/I8 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.

for drop down

Use Data Validation

Allow: List

Source: =Bedroom_Types

"Bedroom_Types" is named range (See "Formulas"==>"Name manager"

Than you for your quick response as usual John.

I will apply the formula and get back to you you to let you know how it worked.

Cheers!

Hi John,

Thanks again!

I input your formula for MAX in cell J7, choosing a date range for the month of March and the bedroom type "Bachelor" and I was expecting the result to be 5.4%, however it's coming in as 0. I am sure I might have done something incorrectly and please do forgive my ignorance.

Is it possible for you to do the formulas on the attached spreadsheet and send it back to me so that I can understand how it works?

Cheers!

as below .....

.. I7/I8 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.

I know I was making a mistake! My apologies.

As before, it worked like a charm.

Thank you once again and have a wonderful weekend!

Jeptik

Hi John,

My apologies for reopening this thread again! My boss is asking for more data points and analysis.

I have a formula that counts the number of occurrences based on the dates for each location and the date period set, that returns a result.

I now need to add the bedroom type into the mix so that, as before, when I change the dates and bedroom type, the data updates accordingly.

I am attaching a spreadsheet to illustrate what is required.

Your help is once more sought and of course, appreciated. Pity I can keep repping you every time you have assisted me on this.

Thanks.

jeptik

The last file I posted chooses bedroom from the drop down

=IFERROR(MAX(IF(($B:$B>=$I$3)*($B:$B<=$J$3)*($A:$A=$A2)*($F:$F=$L$3),$C:$C)),"")

Thanks for the quick response John.

So am I to assume it's just a matter of using the same countifs formula and just reference the bedroom type? I am a bit confused as to what the formula would look like in this case.

Can you be so kind as to write it out for me please as I am scratching my head on how it should be.

Thanks.

The formulas are in the file I posted.

However, they now will need to changed to cater for the ALL selection:

For MAX

=IFERROR(IF($L$3="ALL",MAX(IF(($B:$B>=$I$3)*($B:$B<=$J$3)*($A:$A=$A6),$C:$C)),MAX(IF(($B:$B>=$I$3)*($B:$B<=$J$3)*($A:$A=$A6)*($F:$F=$L$3),$C:$C))),"")

For MIN

=IFERROR(IF($L$3="ALL",MIN(IF(($B:$B>=$I$3)*($B:$B<=$J$3)*($A:$A=$A7),$D:$D)),MIN(IF(($B:$B>=$I$3)*($B:$B<=$J$3)*($A:$A=$A7)*($F:$F=$L$3),$D:$D))),"")

Do you want the same for AVERAGE

=IF($L$3="ALL",AVERAGEIFS($E:$E,$B:$B,">=" &$I$3,$B:$B,"<=" &$J$3,$A:$A,$A2),AVERAGEIFS($E:$E,$B:$B,">=" &$I$3,$B:$B,"<=" &$J$3,$A:$A,$A2,$F:F,$L$3))

AVERAGE by ROOM

Hi John,

Thanks for expanding on those formulas. That certainly does help, however the attached excel file here is of a different scenario where countifs are used to determine the count of vacancies and I am confused on how this would apply to the formula I have in the file factoring in the bedroom type.

I am sorry if I am not understanding this, but if you can put the formula in cell E5 for me, I would appreciate it.

Thanks.

=IF($J$2="ALL",COUNTIFS($A:$A,A2,$B:$B,">="&$G$3,$B:$B,"<="&$H$3),COUNTIFS($A:$A,A2,$B:$B,">="&$G$3,$B:$B,"<="&$H$3,$C:$C,$J$2))


NOW, please study these formulae and try to understand how they work.

Thank you very much John.

I certainly will as you are right, I do need to understand how it works.

Cheers!