Separate into 3 separate COUNTIF functions for the 3 different ranges.. then add the SUM() part
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Any chance of an example. Tried it a number of times over the weekend but just not getting anywhere.
My brain hurts
cheers people
bud
Since you have single references, try:
Code:=SUMPRODUCT((COUNTIF(B20:F20,{"HH","HS","HO"})+(L20={"HH","HS","HO"})+(AD20={"HH","HS","HO"})+(AF20={"HH","HS","HO"})*0.5)+SUM(B20:F20,L20,AD20,AF20))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi NBVC
Thanks for that.
I have put that formula in but I am getting a total of 22. I should be getting a total off 7.5. I can't quite see where I am going wrong.
Please attach actual worksheet showing the problem... not a .jpg file... an .xls file please.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi
Attached is the sheet.
cell AO20 should have a total of 7.5 not 22. The formula look ok to me but it does go over my head a bit.
Cheers
bud
Misplaced a bracket or two...Try:
Code:=SUMPRODUCT((COUNTIF(B20:F20,{"HH","HS","HO"})+(L20={"HH","HS","HO"})+(AD20={"HH","HS","HO"})+(AF20={"HH","HS","HO"})))*0.5+SUM(B20:F20,L20,AD20,AF20)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks again.
Last query I promise.
If I put 0.5 on one for the cells for example E20. It doesn't calculate it in AO20 :S any ideas
I wished I hadn't started this sheet now lol.
When I put 0.5 in E20, AO20 changed to 7.0 (from 7.5). Not right?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
NBVC!!!
My bad. It is working like a dream.
Thank you so much for all your help.
Bud
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks