A screenshot of my sample data is attached.
This is my formula that counts the number in the cell if it's preceeded with a 'H'.
=SUMPRODUCT((LEFT(B5:G5,1)="H")*(RIGHT(B5:G5,LEN(B5:G5)-1)))
The only problem is that it returns #VALUE if one of the cells in the range B5:G5 is blank.
The range is much larger on my actual data but I'm just working with sample data to get the formula right.
Any help much appreciated.
Last edited by A2H GO; 10-29-2011 at 05:41 AM.
If you only have one condition, why not use COUNTIF()?
=COUNTIF(B5:G5,"H*")
Hi, thanks for your help.
My formula needs to count the number next to the letter in each cell.
So in the row the cells might contain H5, H5, H1 etc, so the total would be 11.
Its a holiday planner so people enter either H for Holiday followed by the number of hours or a selection of other prefixes like T for Training.
I'm then using this formula to create a summary of 'Holiday' hours and 'Training' hours etc.
One way:
=SUM(IF(LEFT(B5:G5,1)="H",--RIGHT(B5:G5,LEN(B5:G5)-1)))
confirmed with CTRL+SHIFT+ENTER not just ENTER
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
That works perfectly! Many thanks!
My only worry is that when new people are added to the sheet, the formulas are just dragged down, will this still be OK given I had to press CTL + SHIFT when entering?
I just tried dragging it down and it seems fine...
Yes, once you confirmed it the first time, you can copy it down and it will "hold" the confirmation..
Brilliant. Can you leave 'Rep' on here? Or mark this as solved?
You can do both.
To leave rep, click the "libra or balances" icon.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
This version should work OK with blanks......retaining SUMPRODUCT so you don't need "array entry"
=SUMPRODUCT((LEFT(B5:G5)="H")+0,(0&MID(B5:G5,2,9))+0)
Audere est facere
Both work really well, I think Ill go with the second to make it easier for other people to amend the worksheet.
The only problem I'm having is when quarter of hours are entered, eg. H7.25 for 7 and a quarter hours, it rounds up to 7.3....
Edit, that was easy enough, I just needed to format the cell to 2 decimal places!
Last edited by A2H GO; 10-29-2011 at 05:19 AM.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks