Search results

Solution found - I can leave blank cells provide I put a value in column E. I have put 0.0009% in place of N/a, that way the formula works fine. If you have blanks in both columns the formula adds the cell in Column G as it is 100% of the cell in Column E, which I'm sure is what you trying to...

Thanks for that - it does work perfectly if I remove the N/a's. But I need to have N/a or something that will not be counted (a zero is no good either)The reason I need this is that sometimes an area does not report their figures for one month, but do so for the next month - my solution was to...

I still don't see what you are getting at, whihc are the right columns? - can you make the changes and post the file back to a free sharing site so I can see what you mean?

Don't know what you mean by your last post - did you see what I replied?

See what you mean - even left just blank it creates a problem. Is there an alternative formula or a way to not count - such as ISBLANK? Also, not sure what you mean with the sumproduct - it should be counting column A if column F is between 100 and 105% of column E not G. Once I can get this...

Sorry, I was away for the weekend - I have now changed this to Public. Can I also ask you if it is possible for me to get an automtatic email notification when anyone sends a reply or addds to a post? I have searched to see if the forum does this facitlity, but cannot find anything. I apologise...

Sorry for the delay, I had to sort out a file sharing site. This is the link to the file: http://www.keepandshare.com/doc/2056565/improvement-over-nov-2009-xls-july-9-2010-1-25-pm-169k?da=y

Tried what you suggested but still no change - I did check the cells and they were formatted as a percentage. I don't mind sending the file if it helps to identify where I am going wrong.

Still getting the same error - I will have time later to look into it and will come back to you

I get a #VALUE!error. Could it be because the data in each cell is a percentage and the cell is formatted as a percentage

I need to compare the percentatge value in column G with column F and then add up the toals of all cells that match the criteria. The comparison is that the value in each cell in the range in column G is between 100% and 105% of the value in Column F. I though if I insert another column E...

I did try just the one column first and found that if any cell in column B was blank it would still include it in the total, even though the row was hidden. The only way I found to work round this is the solution I gave. It may be that I have made an error somewhere

kchiba - thanks for your comments, but as already stated I would have to change the criteria in the calculation each time. cyrilz - you pointed me in the right direction - grateful thanks. The link you gave shows how to use an extra column next to your data and write the formula...

Just tried your suggestion on using the autofilter criteria - the Dealer names are in column A, in this case the filtered data was for a dealer named Harpers and it works! =SUMPRODUCT((D3:D117>=80%)*(D3:D117<82.5%)*(A3:A117="Harpers")) But as I have observed already, I would have to...

Just another observation - if I put the Autofilter criteria into the calculation, say Dealer, if I change the filter I would have to change the criteria in the calculation and again each time I change the filter

Not sure on the criteria to use. I have applied an autofilter to the headings in row 2. The headings are Dealer,Person, then Jan Feb Mar etc. I want to filter by just one heading at a time, for example I could select just the Dealer to show his results and then the COUNTIF and SUMPRODUCT to...

These are 2 formula that I am using =COUNTIF(D3:D117,"<80%") =SUMPRODUCT((D3:D117>=80%)*(D3:D117<82.5%) I have now applied an autofilter to the data, however my calculations still calculate all the rows, including the hidden ones. I have seen several suggestions to use...

I'm using a spreadsheet for project management. What would be useful is if I could highlight the changes since the last update so that people would not have to trawl through the sheet to find out what has changed. I have looked at conditional formatting but have not been able to identify a...