Hi All
Thanks in advance for reading this.
I need a IF formula to add up a selection of cells IF the value is <=2 put the result in AO8, But if the value is >=2.001 put the value in AN8
Can anyone help? I've attached my sheet which gives a clearer indication of what I'm trying to achieve.
Thanks
Sam
Which range of cells are you looking at?
AO8: =sumif(range,"<=2",range)
AN8: =sumif(range,">2",range)
Obviously, substitute range in the formulas for the range of cells you're interested in.
Last edited by BadlySpelledBuoy; 02-07-2014 at 08:00 AM.
Cell Range F8:I8,K8:P8,R8:W8,Y8:AD8,AF8:AJ8
I don't want it to include the Sundays
Last edited by sambo24; 02-07-2014 at 08:04 AM.
Having had a closer look at your workbook, this problem is not as easy to solve as I first thought. SUMIF wont work with non contiguous ranges.
Will the days of the week ALWAYS be laid out this way? i.e. always a Wednesday in column F?
BadlySpelledBuoy - Not every month, it would change but perhaps I'd have to accept to having to amend the formulas each month.
If I could just get a formula to work on this sheet then I'll be happy to adjust it to work on future months.
Thanks
Is the <=2 or >=2.001 based on the overall total or the total for a week?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
The easis way is to change "S" for Sunday to "Su" then use
=SUMPRODUCT(--($F$3:$AJ$3<>"Su"),--($F$8:$AJ$8>=2),--(F8:AJ8))
=SUMPRODUCT(--($F$3:$AJ$3<>"Su"),--($F$8:$AJ$8<2),--(F8:AJ8))
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Do you have any scope to make a minor amendment to this workbook?
If so, it would be easy to have a formula that does that you want that you WOULDN'T have to change each month.
If you can change the Sundays from "S" to "Su" then you could use the below formulas:
AN8: =SUMIFS(F8:AJ8,F8:AJ8,"<=2",F3:AJ3,"<>Su")
AO8: =SUMIFS(F8:AJ8,F8:AJ8,">2",F3:AJ3,"<>Su")
EDIT: Robert just beat me to it with a very similar solution.
Hi,
maybe you could try -keeping in AD2 a reference date:
Please Login or Register to view this content.RegardsPlease Login or Register to view this content.
Last edited by canapone; 02-07-2014 at 08:34 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Sambo24, I tried both formulas and depending on the results you are looking for they both give results. But I'd recommend that if you want to autopopulate the Day cells you use Wed, Thu, Fri. These Excel will recognize and when you drag to the right will autopopulate the Day row. You'll just have to adjust the formulas both BSB and Robert gave you.
Based on the Day total
Based on the Day total
Based on the Day totalOriginally Posted by Sambo kid
Works a Treat!
Thanks
=SUMPRODUCT(--($F$3:$AJ$3<>"Su"),--($F$8:$AJ$8<2),--(F8:AJ8))
If I wanted to exclude S & Su from the sum what would it change it too?
If you changed the headers for Sunday to Su rather than S then you can amend the SUMPRODUCT to:
=SUMPRODUCT(--($F$3:$AJ$3<>"S"),--($F$3:$AJ$3<>"Su"),--($F$8:$AJ$8<2),--(F8:AJ8))
Or, use the shorter SUMIFS with a wildcard:
=SUMIFS(F8:AJ8,F8:AJ8,"<=2",F3:AJ3,"<>S*")
Hi Guys, I know I've marked this thread as 'SOLVED' but I have another question concerning it........let hope you receive this!
The formula =SUMIFS(F8:AJ8,F8:AJ8,"<=2",F3:AJ3,"<>S*") works a treat but......I have a Bank Holiday coming up and I need to exclude one particular Monday in that month.
I would like the above formula to ignore Row 3 IF Row 4 contains 'BH'
Thanks in Advance
=sumifs(f8:aj8,f8:aj8,"<=2",f3:aj3,"<>s*",f4:aj4,"<>bh")
Why isn't this working?
=IF(AH6+AI6>162.95,SUMPRODUCT(--($C$4:$AG$4<>"S"),--($C$4:$AG$4<>"Su"),--($C$3:$AG$3<>"BH"),--(SUM(C6:AG6)-AJ6),)"")
Its the Sum bit at the end that is causing problems --(SUM(C6:AG6)-AJ6)
if I change it to --(C6:AG6)) it works fine, but I need to subtract AJ6 from the SUM.
Any Ideas?
Thanks in Advance
Last edited by sambo24; 03-21-2014 at 07:45 AM.
Al arrays must be the same length.
You can use
=IF(AH6+AI6>162.95,SUMPRODUCT(--($C$4:$AG$4<>"S"),--($C$4:$AG$4<>"Su"),--($C$3:$AG$3<>"BH"),--(C6:AG6-AJ6)),)"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks