Search results

Thanks Hui. The formula works fine for counting those cells that are red, but doesn't work for those that are yellow. I have tried to manipulate the formula but no luck. I have attached an updated version of the Test spreadsheet with your formulas included as well as the formulas I am using...

Hi All, I've searched multiple sites looking for ideas on how to count cells that have a specific colour based on conditional formatting - but there seems to be no way to do this. I have a VBA to count cell colours that I change manually and this works fine, but I am after some assistance in...

Ok I have come up with a work around and just had the Total count all text containing "Completed" =SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)),ISNUMBER(SEARCH("Completed",B6:B19))+0) Then for Specifically identifying those that are "Completed" and "Not Completed" I...

Thanks Narayan, That formula works but when entered it only counts the exempt cells. For the Total in column E1 I would like to only count the Completed and Non completed. Any other ideas? Thanks

Hi All, I have a training spreadsheet where it counts those that have completed, not completed or are exempt from the training. See attached spreadsheet. What I would like to be able to do is filter by the Business Unit to see who has completed, not completed or is exempt. Without filtering...

Thanks for your help Narayan! I don't fully understand the logic in the formula you used but it is working and I have been able to modify it to my version training register :DD

Hi all, I am using Excel 2010 and I have a training register with Name, Training A & Training B. Training A expires after 2 years, Training B expires after 3 years. I would like to set up conditional formatting so that when these are expired the cell colour changes to RED, and when they are 1...

Thanks for your feedback Somendra and yes you are right I did mean row 6. Think I was just having a brain fade as I just realised the copy/paste way will work just fine. Thanks again :)

Hi All, I am working on some data that is set out on a weekly basis and calculates averages in some of the columns. Instead of retyping the formulas each week I was hoping to copy the formula but change the cells it references without manually typing them for the every week. EG: cell F6 is an...

That's great Hui! Thanks so much for your help as I was able to modify your formula to line up with my existing database and it worked a treat :)

Hi Excel guru's :) I have a question which I'm not even sure is possible relating to an Excel database I'm developing. Some background info: - I have used a variety of formulas to develop a summary table - part of the summary table displays Tasks that are overdue What I would like to do is...

:DDThanks for all your help Nebu

Thanks for that Nebu its exactly what I am after! I'm not all that familar with pivot tales - do you know of a good link/resource that might be able to step me through the process of setting this up?

Hi All I am having problems coming up with the right fomula for counting a column with text only if it matches a certain name in another column. My current formula is =SUMPRODUCT(($B$1:$B$37)*($A$1:$A$37="Robin")) and I have attached a test sheet fo an example. Any ideas/help would be great...

Finally got this one to work as well! Had some of the subtotals linking to wrong the cells. Now the fun stuff begins - trying to get the spreadsheet to use the right data to create the graphs! :) Thanks a million for all your help Narayan

No need to worry i have finally solved the problem. Pretty sure i am going abot it in the long way as i am just using a SUM to total a few other cells with SUMPRODUCT results. Thanks for all your help it has been appreciated

Thought i had it...but nope :( this is the formula i am currently using =E250/(SUBTOTAL(103,OFFSET(G2,ROW(G2:G236)-ROW(G2),0))) trying to get the cells in E250 to divide by the subtotal but all i get is #DIV/0! Any ideas?

Thanks Narayan. Think i must have been staring at this spreadsheet for too long! As soon as i read your post i realised i had the data already on the spreadsheet and just needed to do a simple SUM statement........ Cheers

Is there a formula you can use to calculate the percentage of data that is only visible on the spreadsheet after it has been filtered? For Example I have a SUBTOTAL in Column A, Locations in Column B, and data in Column C Column B Column C Los Angeles 15 Los Angeles 10...

Thansk for the feedack. the formula is meant to look at the values in column (U2:U236)and (V2:V236) and if the data in column 'U' on one particulr ro is greater than or equal to 140, and the data on the same row in colum 'V' is greater than or equal to 90 then it will add it to the total...

I am having problems with the below SUMPRODUCT returning an incorrect total. =SUMPRODUCT(--($U$2:$U$236>=140)*($V$2:$V$236>=90),SUBTOTAL(103,OFFSET(U2,ROW(U2:V236)-ROW(U2),0))) It should return a total of 42 but is only giving a result of 22. Can anyone see a problems with this?

Thanks again Narayan you've been a massive help!

Apologies for the many posts recently but have been dumped with putting a report together asap. Think i have been staring at this one for too long and can't work out whats missing. Any help would be great appreciated...

Awesome. Thanks again mate!

I am using this SUMPRODUCT to find numbers >=10 =SUMPRODUCT(--(N2:N236>=10),SUBTOTAL(103,OFFSET(N2,ROW(N2:N236)-ROW(N2),0))) any ideas how i can modify this so that it counts only the data cells that are >=10 and <=12 Cheers