Not sure if title is concise but.....
What I want is a formula to go down a column and countif
"vacation" then *7.5hours and also to countif "Vacation",2 (if only the word vacation is there then *7.5 and if vacation is followed by a different number then add that number so that the following would be totaled into one cell
Example
Cell 1 = Vacation
Cell 2 = Vacation,2
The total would be 9.5 (7.5hours for cell 1 and 2 hours for cell 2)
Thank you in advance for any help I can get on this.
Lori
I assume the cells are in a row e.g A1,B1 with sum in C1?
or is it a total sum for multiple rows
be totaled into one cell
Last edited by JohnTopley; 01-18-2016 at 03:15 PM.
I want the formula to count down a column. Like B25: B389. Then I can copy the formula across the rows that contain my employees
Joe Mary Ralph
January
1 Vacation
2 Vacation,2
3
February
1
2
3
march
1
2
3
The word vacation would always equal 7.5 hours, but Vacation,2 would equal 2. So the total vacation hours would be 9.5 in this example
Lori
@ lori can you upload a sample book
Go to advanced tab and click manage attachment
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Yes, here it is....
Hope the attachment worked
two vacation cells would equal to 15 hours and and one vacation,2 cell would equal to 2 hour altogether should be equal to 17 hours
Isn't it
try below
=(COUNTIF(B25:B402,"Vacation")*7.5)+(COUNTIF(B25:B402,"Vacation,2")*2)
or you can use
=SUMPRODUCT(((B25:B402)={"vacation","vacation,2"})*{7.5,2})
Yes you are correct....17 hours, my mistake. And thank you so much for the formulas. I will give them a try
Lori
You are welcome Lori and thanks for the feedback
Also....what if the 2 was another number.... Is there something that would cover any other number? It will not always be 2, it could be 3, or 4, etc.
thanks
=SUMPRODUCT(((B25:B402)={"vacation","vacation,2"})*{7.5,2})
whenever it encounters,VACATION will be multiplied by 7.5 and , Vacation,2 will be multiplied by 2 you can change them accordingly in {7.5,2} to lets say {1,3} or whatever your need is
hope this helps.
if this takes care of your question then you can mark thread solved and how to do that you can read my signatures in post#4
If you have many more different entries, apart from ,2, then perhaps consider using a helper column to extract that value from the string?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Following Ford's suggestion:
in C25 and copy down
=IFERROR(IF(LEFT(B25,8)="Vacation",SUBSTITUTE(B25,"Vacation,","")+0,""),"")
in B20
=COUNTIF(B25:B47,"Vacation")*7.5+SUM(C25:C47)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks