COUNTIFS with Hyperlink

[mark888]

Hi All,

Again I come to you for some Excel help. I have a file as attached below. Basically I am trying to do two things and it is not working out.

Test1.xlsx

1) In tab Summary, I have tried COUNTIFS formula to calculate the count based on a certain text in Column C&D and month but it does not show the correct value.(Could be the Value format is wrong?) I want to remain the raw data as it is if possible as this is being extracted by someone else.
2) From there on I would like to have each of the count value to have a hyperlink to the raw data and only show those data. I do know Pivot table is another option but would like to know if this is possible or not. :-)

Hope someone can help me out there. :-)

Kind Regards,
Mark.

[AlKey]

Hi Mark

on your Row Data sheet enter in D2 and copy down

Please Login or Register  to view this content.

then on summary sheet enter formula in E5 and copy it across and then down

Please Login or Register  to view this content.

[mark888]

Hi Mark

on your Row Data sheet enter in D2 and copy down

Please Login or Register  to view this content.

then on summary sheet enter formula in E5 and copy it across and then down

Please Login or Register  to view this content.

Hi Alkey,

Yes that works fine with the Helper cell but is there anyway we can use without a new column D in Raw Data? Also is it possible to have hyperlink for the values in tab Summary?

Kind Regards,
Mark.

[mark888]

Hi Alkey,

Yes that works fine with the Helper cell but is there anyway we can use without a new column D in Raw Data? Also is it possible to have hyperlink for the values in tab Summary?

Kind Regards,
Mark.

Hi Alkey,

Also i tried changing the data in Raw Data to different years (e.g. 2015 or 2017) and it still calculates in the summary tab so i don;t think its taking only those with year 2016. :-(

Kind Regards,
Mark.

[AlKey]

Try this one
it will take year into consideration

Please Login or Register  to view this content.

[mark888]

Try this one
it will take year into consideration

Please Login or Register  to view this content.

Hi Alkey,

Yes this one works perfectly without the use of helper cells and the year is correct as well. Anything can be done on the hyperlinking part? Was thinking if there is a formula we can integrate to hyperlink the values so when it is clicked, it will only show the data of that month.

Example - Hyperlink Column E5 in Summary tab and when clicked on the hyperlink it will only show data for that detail.

Kind Regards,
Mark.

[AlKey]

I don't think the idea with a hyperlink is doable. At least for me, I wouldn't know where to start.

[mark888]

Hi Alkey,

Yes this one works perfectly without the use of helper cells and the year is correct as well. Anything can be done on the hyperlinking part? Was thinking if there is a formula we can integrate to hyperlink the values so when it is clicked, it will only show the data of that month.

Example - Hyperlink Column E5 in Summary tab and when clicked on the hyperlink it will only show data for that detail.

Kind Regards,
Mark.

Hi Alkey,

Sorry one more thing...the SUMPRODUCT formula doe snot allow me to take the whole column range like A:A so the range must be specified. In this case since data will always change, is there a way to set this without using range?

Kind Regards,
Mark.

[AlKey]

Then you have to use COUNTIFS function. There nothing wrong with a helper column. If you can't have it col D put it any other column. If you don't to see it at all hide that column.

You can change $D:$D to any other column.

Please Login or Register  to view this content.

[mark888]

Then you have to use COUNTIFS function. There nothing wrong with a helper column. If you can't have it col D put it any other column. If you don't to see it at all hide that column.

You can change $D:$D to any other column.

Please Login or Register  to view this content.

Hi Alkey,

tried the new COUNTIF formula and it does not calculate even with the helper cell in column D.

Kind Regards,
Mark.

[AlKey]

change the formula in the helper column to: =TEXT(B2,"mmm-YYYY")

[mark888]

change the formula in the helper column to: =TEXT(B2,"mmm-YYYY")

Hi AlKey,

Yes this works perfectly now. I will try to find the hyperlinking another way.
Appreciate your help. :-)

Kind Regards,
Mark.