Help needed with a SUMIF(AND

[Francois via OfficeKB.com]

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",P15365)+
SUMIF($H15:$H365,"L2DAYS",P15365)+
SUMIF($H15:$H365,"L3DAYS",P15365)+
SUMIF($H15:$H365,"L4DAYS",P15365)+
SUMIF($H15:$H365,"L5DAYS",P15365)+
SUMIF($H15:$H365,"L6DAYS",P15365)+
SUMIF($H15:$H365,"L7DAYS",P15365)+
SUMIF($H15:$H365,"L8DAYS",P15365)+
SUMIF($H15:$H365,"L9DAYS",P15365)+
SUMIF($H15:$H365,"L10DAYS",P15365)+
SUMIF($H15:$H365,"L11DAYS",P15365)+
SUMIF($H15:$H365,"L12DAYS",P15365)+
SUMIF($H15:$H365,"L13DAYS",P15365)+
SUMIF($H15:$H365,"L14DAYS",P15365)

Thanks for anyone's help

 

[toppers]

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",P15365)+
SUMIF($H15:$H365,"L2DAYS",P15365)+
SUMIF($H15:$H365,"L3DAYS",P15365)+
SUMIF($H15:$H365,"L4DAYS",P15365)+
SUMIF($H15:$H365,"L5DAYS",P15365)+
SUMIF($H15:$H365,"L6DAYS",P15365)+
SUMIF($H15:$H365,"L7DAYS",P15365)+
SUMIF($H15:$H365,"L8DAYS",P15365)+
SUMIF($H15:$H365,"L9DAYS",P15365)+
SUMIF($H15:$H365,"L10DAYS",P15365)+
SUMIF($H15:$H365,"L11DAYS",P15365)+
SUMIF($H15:$H365,"L12DAYS",P15365)+
SUMIF($H15:$H365,"L13DAYS",P15365)+
SUMIF($H15:$H365,"L14DAYS",P15365)

Thanks for anyone's help

try:

=SUMPRODUCT(--($H15:$H365>="L1Days"),--
($H15:$H365<="L13Days"),P15365)

 

[Francois via OfficeKB.com]

Can anyone advise on a better way of expressing this formula?

[quoted text clipped - 22 lines]try:

=SUMPRODUCT(--($H15:$H365>="L1Days"),--
($H15:$H365<="L13Days"),P15365)

Thanks for your reply, but I can't get it to work, it just displays a
blank (no value) cell.

 

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(MATCH(SUBSTITUTE($H15:$H36,"Days",""),"L"&ROW(INDIRECT("1:13")),0))),P1536)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Can anyone advise on a better way of expressing this formula?

[quoted text clipped - 22 lines]try:

=SUMPRODUCT(--($H15:$H365>="L1Days"),--
($H15:$H365<="L13Days"),P15365)

Thanks for your reply, but I can't get it to work, it just displays a
blank (no value) cell.

 

[Bob Phillips]

or simpler ...

=SUMPRODUCT(--(ISNUMBER(MATCH($H15:$H36,"L"&ROW(INDIRECT("1:13"))&"Days",0))),P1536)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Can anyone advise on a better way of expressing this formula?

[quoted text clipped - 22 lines]try:

=SUMPRODUCT(--($H15:$H365>="L1Days"),--
($H15:$H365<="L13Days"),P15365)

Thanks for your reply, but I can't get it to work, it just displays a
blank (no value) cell.

 

[Roger Govier]

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),P15365)

 

[Francois via OfficeKB.com]

=SUMPRODUCT(--(ISNUMBER(MATCH(SUBSTITUTE($H15:$H36,"Days",""),"L"&ROW(INDIRECT("1:13")),0))),P1536)
[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

 

[Francois via OfficeKB.com]

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),P15365)
[quoted text clipped - 29 lines]

=SUMPRODUCT(--($H15:$H365>="L1Days"),--
($H15:$H365<="L13Days"),P15365)

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

 

[Francois via OfficeKB.com]

or simpler ...

=SUMPRODUCT(--(ISNUMBER(MATCH($H15:$H36,"L"&ROW(INDIRECT("1:13"))&"Days",0))),P1536)
[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

 

[Bob Phillips]

That looks like a typo

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

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),P15365)

Can anyone advise on a better way of expressing this formula?

[quoted text clipped - 29 lines]

=SUMPRODUCT(--($H15:$H365>="L1Days"),--
($H15:$H365<="L13Days"),P15365)

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

 

[Francois via OfficeKB.com]

That looks like a typo
[quoted text clipped - 14 lines]

I do appreciate all you guys helping

Never mind, It's all working now

 

[Roger Govier]

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

That looks like a typo

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

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),P15365)

Can anyone advise on a better way of expressing this formula?

[quoted text clipped - 29 lines]
=SUMPRODUCT(--($H15:$H365>="L1Days"),--
($H15:$H365<="L13Days"),P15365)

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