I think the formula I am searching for would be a combination of vlookup and countif, I was wondering if anyone could take a look at the excel file example and recommend suggestions.
Thank you very much for reading this.
I think the formula I am searching for would be a combination of vlookup and countif, I was wondering if anyone could take a look at the excel file example and recommend suggestions.
Thank you very much for reading this.
I don't understand your I:K table. What does 'Next Result' mean? Where do the 'Times' come from? How does the 'C2' in G8 relate to the table?
Your 'total' is only a total of the previous 3 rows - did you mean it to be a total of all the rows, which would then also make the percentages add to just 100% instead of the current 200%?
Please try to clarify better what you are trying to achieve, so we can try to help.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
sorry c2 is data not a cell.
the I:K table is basically looking up the data c2 and counting how many times different results are under c2.
So for example if you look at column A and search for C2 you'll see the result A1 came after C2 one time.
in this example data c2 is in cell A8 and data a1 is in cell A9
I hope this helps, sorry if I'm not explaining it well. Basically I want to search certain data from the list and count the results that come under that search.
Last edited by formexcel; 07-06-2017 at 08:08 AM.
j8=COUNTIFS($A$1:$A$78,$G$8,$A$2:$A$79,$I8)
or
=SUMPRODUCT(($A$1:$A$78=$G$8)*($A$2:$A$79=$I8))
and copy towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thanks these work perfectly, but then id need to update the formula every time I added more data to column A. Is there a way round this, so I can look up A:A for example?
Turn your data into an Excel table and then reference the column by its table name -the range will then adapt when rows are added.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks for the rep!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks