| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed with a SUMIF(AND
Can anyone advise on a better way of expressing this formula?
All I want to do is sum entries in a column where the criteria is between L1DAYS and L13 DAYS (inclusive). I assume that I need some sort of SUMIF(AND but I can't figure it out =SUMIF($H15:$H365,"L1DAYS",P15:P365)+ SUMIF($H15:$H365,"L2DAYS",P15:P365)+ SUMIF($H15:$H365,"L3DAYS",P15:P365)+ SUMIF($H15:$H365,"L4DAYS",P15:P365)+ SUMIF($H15:$H365,"L5DAYS",P15:P365)+ SUMIF($H15:$H365,"L6DAYS",P15:P365)+ SUMIF($H15:$H365,"L7DAYS",P15:P365)+ SUMIF($H15:$H365,"L8DAYS",P15:P365)+ SUMIF($H15:$H365,"L9DAYS",P15:P365)+ SUMIF($H15:$H365,"L10DAYS",P15:P365)+ SUMIF($H15:$H365,"L11DAYS",P15:P365)+ SUMIF($H15:$H365,"L12DAYS",P15:P365)+ SUMIF($H15:$H365,"L13DAYS",P15:P365)+ SUMIF($H15:$H365,"L14DAYS",P15:P365) Thanks for anyone's help -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200706/1 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
On Jun 6, 11:23 am, "Francois via OfficeKB.com" <u18959@uwe wrote:
Can anyone advise on a better way of expressing this formula? All I want to do is sum entries in a column where the criteria is between L1DAYS and L13 DAYS (inclusive). I assume that I need some sort of SUMIF(AND but I can't figure it out =SUMIF($H15:$H365,"L1DAYS",P15:P365)+ SUMIF($H15:$H365,"L2DAYS",P15:P365)+ SUMIF($H15:$H365,"L3DAYS",P15:P365)+ SUMIF($H15:$H365,"L4DAYS",P15:P365)+ SUMIF($H15:$H365,"L5DAYS",P15:P365)+ SUMIF($H15:$H365,"L6DAYS",P15:P365)+ SUMIF($H15:$H365,"L7DAYS",P15:P365)+ SUMIF($H15:$H365,"L8DAYS",P15:P365)+ SUMIF($H15:$H365,"L9DAYS",P15:P365)+ SUMIF($H15:$H365,"L10DAYS",P15:P365)+ SUMIF($H15:$H365,"L11DAYS",P15:P365)+ SUMIF($H15:$H365,"L12DAYS",P15:P365)+ SUMIF($H15:$H365,"L13DAYS",P15:P365)+ SUMIF($H15:$H365,"L14DAYS",P15:P365) Thanks for anyone's help -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200706/1 try: =SUMPRODUCT(--($H15:$H365="L1Days"),-- ($H15:$H365<="L13Days"),P15:P365) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
wrote:
Can anyone advise on a better way of expressing this formula? [quoted text clipped - 22 lines] -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200706/1 try: =SUMPRODUCT(--($H15:$H365="L1Days"),-- ($H15:$H365<="L13Days"),P15:P365) Thanks for your reply, but I can't get it to work, it just displays a blank (no value) cell. -- Message posted via http://www.officekb.com |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
=SUMPRODUCT(--(ISNUMBER(MATCH(SUBSTITUTE($H15:$H36,"Days",""),"L "&ROW(INDIRECT("1:13")),0))),P15:P36)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Francois via OfficeKB.com" <u18959@uwe wrote in message news:734a76257e601@uwe... wrote: Can anyone advise on a better way of expressing this formula? [quoted text clipped - 22 lines] -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200706/1 try: =SUMPRODUCT(--($H15:$H365="L1Days"),-- ($H15:$H365<="L13Days"),P15:P365) Thanks for your reply, but I can't get it to work, it just displays a blank (no value) cell. -- Message posted via http://www.officekb.com |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
or simpler ...
=SUMPRODUCT(--(ISNUMBER(MATCH($H15:$H36,"L"&ROW(INDIRECT("1:13") )&"Days",0))),P15:P36) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Francois via OfficeKB.com" <u18959@uwe wrote in message news:734a76257e601@uwe... wrote: Can anyone advise on a better way of expressing this formula? [quoted text clipped - 22 lines] -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200706/1 try: =SUMPRODUCT(--($H15:$H365="L1Days"),-- ($H15:$H365<="L13Days"),P15:P365) Thanks for your reply, but I can't get it to work, it just displays a blank (no value) cell. -- Message posted via http://www.officekb.com |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
Hi John
I don't think that will work with Sumproduct. I think it would need to be something like =SUMPRODUCT(--(--(MID(H15:H365,2,SEARCH("D",H15:H365:I24)-2))=1), --(--(MID(H15:H365,2,SEARCH("D",H15:H365)-2))<=15),P15:P365) -- Regards Roger Govier wrote in message ps.com... On Jun 6, 11:23 am, "Francois via OfficeKB.com" <u18959@uwe wrote: Can anyone advise on a better way of expressing this formula? All I want to do is sum entries in a column where the criteria is between L1DAYS and L13 DAYS (inclusive). I assume that I need some sort of SUMIF(AND but I can't figure it out =SUMIF($H15:$H365,"L1DAYS",P15:P365)+ SUMIF($H15:$H365,"L2DAYS",P15:P365)+ SUMIF($H15:$H365,"L3DAYS",P15:P365)+ SUMIF($H15:$H365,"L4DAYS",P15:P365)+ SUMIF($H15:$H365,"L5DAYS",P15:P365)+ SUMIF($H15:$H365,"L6DAYS",P15:P365)+ SUMIF($H15:$H365,"L7DAYS",P15:P365)+ SUMIF($H15:$H365,"L8DAYS",P15:P365)+ SUMIF($H15:$H365,"L9DAYS",P15:P365)+ SUMIF($H15:$H365,"L10DAYS",P15:P365)+ SUMIF($H15:$H365,"L11DAYS",P15:P365)+ SUMIF($H15:$H365,"L12DAYS",P15:P365)+ SUMIF($H15:$H365,"L13DAYS",P15:P365)+ SUMIF($H15:$H365,"L14DAYS",P15:P365) Thanks for anyone's help -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200706/1 try: =SUMPRODUCT(--($H15:$H365="L1Days"),-- ($H15:$H365<="L13Days"),P15:P365) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
Bob Phillips wrote:
=SUMPRODUCT(--(ISNUMBER(MATCH(SUBSTITUTE($H15:$H36,"Days",""),"L "&ROW(INDIRECT("1:13")),0))),P15:P36) Can anyone advise on a better way of expressing this formula? [quoted text clipped - 9 lines] Thanks for your reply, but I can't get it to work, it just displays a blank (no value) cell. Now this is really odd !! I can't get Bob's to work either (I ammended the range to cover rows15 to 365) . but it just seems to display zero -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200706/1 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
Roger Govier wrote:
Hi John I don't think that will work with Sumproduct. I think it would need to be something like =SUMPRODUCT(--(--(MID(H15:H365,2,SEARCH("D",H15:H365:I24)-2))=1), --(--(MID(H15:H365,2,SEARCH("D",H15:H365)-2))<=15),P15:P365) Can anyone advise on a better way of expressing this formula? [quoted text clipped - 29 lines] =SUMPRODUCT(--($H15:$H365="L1Days"),-- ($H15:$H365<="L13Days"),P15:P365) Sorry Roger, I couldn't get this to work either ..What's the reference to cell I24 for ? I do appreciate all you guys helping -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200706/1 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
Bob Phillips wrote:
or simpler ... =SUMPRODUCT(--(ISNUMBER(MATCH($H15:$H36,"L"&ROW(INDIRECT("1:13") )&"Days",0))),P15:P36) Can anyone advise on a better way of expressing this formula? [quoted text clipped - 9 lines] Thanks for your reply, but I can't get it to work, it just displays a blank (no value) cell. That did it !!, Thanks a lot to all -- Message posted via http://www.officekb.com |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
That looks like a typo
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Francois via OfficeKB.com" <u18959@uwe wrote in message news:734adabebd43f@uwe... Roger Govier wrote: Hi John I don't think that will work with Sumproduct. I think it would need to be something like =SUMPRODUCT(--(--(MID(H15:H365,2,SEARCH("D",H15:H365:I24)-2))=1), --(--(MID(H15:H365,2,SEARCH("D",H15:H365)-2))<=15),P15:P365) Can anyone advise on a better way of expressing this formula? [quoted text clipped - 29 lines] =SUMPRODUCT(--($H15:$H365="L1Days"),-- ($H15:$H365<="L13Days"),P15:P365) Sorry Roger, I couldn't get this to work either ..What's the reference to cell I24 for ? I do appreciate all you guys helping -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200706/1 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
Bob Phillips wrote:
That looks like a typo Hi John [quoted text clipped - 14 lines] I do appreciate all you guys helping Never mind, It's all working now -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200706/1 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help needed with a SUMIF(AND
Yes, it was.
I tested using different ranges, and obviously didn't edit very well after copy and paste. It worked fine for me though (well when the correct ranges are used<g) I much prefer your solution though Bob. -- Regards Roger Govier "Bob Phillips" wrote in message ... That looks like a typo -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Francois via OfficeKB.com" <u18959@uwe wrote in message news:734adabebd43f@uwe... Roger Govier wrote: Hi John I don't think that will work with Sumproduct. I think it would need to be something like =SUMPRODUCT(--(--(MID(H15:H365,2,SEARCH("D",H15:H365:I24)-2))=1), --(--(MID(H15:H365,2,SEARCH("D",H15:H365)-2))<=15),P15:P365) Can anyone advise on a better way of expressing this formula? [quoted text clipped - 29 lines] =SUMPRODUCT(--($H15:$H365="L1Days"),-- ($H15:$H365<="L13Days"),P15:P365) Sorry Roger, I couldn't get this to work either ..What's the reference to cell I24 for ? I do appreciate all you guys helping -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200706/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct sumif and what else needed? | Excel Discussion (Misc queries) | |||
| SumIf help needed ( I think) | Excel Discussion (Misc queries) | |||
| help needed on "sumif function with multiple ifs" | Excel Discussion (Misc queries) | |||
| SumIf help needed plz... | Excel Worksheet Functions | |||
| SUMIF help needed | Excel Worksheet Functions | |||
Linear Mode
