F H I J K L
Presidential - Bks 0001 76018 100 82.99
Presidential - Tuit 0001 76018 100 1,020.00 Presidential-border-any 0001 76018 100 642.00

Need what would like

=sumif(F3:F480,"FIND("'Presidential',F3:F480=1",L3:L480)

to do if it were valid syntax.

That is, sum colum L if column F contains text beginning with
"Presidential" irrespective of other qualifiers...


--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

Am Sat, 12 Sep 2020 14:05:54 -0500 schrieb dpb:

F H I J K L
Presidential - Bks 0001 76018 100 82.99
Presidential - Tuit 0001 76018 100 1,020.00 Presidential-border-any 0001 76018 100 642.00

That is, sum colum L if column F contains text beginning with
"Presidential" irrespective of other qualifiers...

try:
=SUMIF(F3:F480,"Presidential*",L3:L480)
or
=SUMPRODUCT((LEFT(F3:F480,12)="Presidential")*L3:L480)


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/12/2020 2:26 PM, Claus Busch wrote:

Hi,

Am Sat, 12 Sep 2020 14:05:54 -0500 schrieb dpb:

F H I J K L
Presidential - Bks 0001 76018 100 82.99
Presidential - Tuit 0001 76018 100 1,020.00
Presidential-border-any 0001 76018 100 642.00

That is, sum colum L if column F contains text beginning with
"Presidential" irrespective of other qualifiers...

try:
=SUMIF(F3:F480,"Presidential*",L3:L480)
or
=SUMPRODUCT((LEFT(F3:F480,12)="Presidential")*L3:L480)

Regards
Claus B.

Thanks, Claus...I hoped I'd beat you back... :)

I forgot to add I need also only when column H is empty -- if it has
something (text) there, we've already found a candidate fund for the scholarship payout...

I'll give the above a go and see if it works right...I figured the
SUMPRODUCT() thingie might be the way to go...is that an array formula I think???

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

Am Sat, 12 Sep 2020 14:49:03 -0500 schrieb dpb:

I forgot to add I need also only when column H is empty -- if it has something (text) there, we've already found a candidate fund for the scholarship payout...

I'll give the above a go and see if it works right...I figured the SUMPRODUCT() thingie might be the way to go...is that an array formula I think???

SUMPRODUCT is an array formula but you don't need to enter it with CTRL+Shift+Enter.

Try:
=SUMIFS(L3:L480,F3:F480,"Presidential*",H3:H480,"")
or
=SUMPRODUCT((LEFT(F3:F480,12)="Presidential")*(H3:H480="")*L3:L480)


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/12/2020 2:26 PM, Claus Busch wrote:

Hi,

Am Sat, 12 Sep 2020 14:05:54 -0500 schrieb dpb:

F H I J K L
Presidential - Bks 0001 76018 100 82.99
Presidential - Tuit 0001 76018 100 1,020.00
Presidential-border-any 0001 76018 100 642.00

That is, sum colum L if column F contains text beginning with
"Presidential" irrespective of other qualifiers...

try:
=SUMIF(F3:F480,"Presidential*",L3:L480)
or
=SUMPRODUCT((LEFT(F3:F480,12)="Presidential")*L3:L480)

Regards
Claus B.

"You dah man!" again, Claus!! :)

=SUMIFS($L$5:$L$480,$F$5:$F$480,"Presidential*",$H$5:$H$480,"")

seems to do the trick...

Thanks.

--dpb

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

Am Wed, 16 Sep 2020 19:38:59 -0500 schrieb dpb:

OK, how to negate a condition?

Say wanted everything EXCEPT "Presidential*"

=SUMIF($F$5:$F$480,NOT("Presidential*"),$L$5:$L$480)

doesn't error, but doesn't return anything but zero, either.

Ended up using

=SUMIF($L$5:$L$480)-SUMIF($F$5:$F$480,"Presidential*",$L$5:$L$480)

try:
=SUMIFS(L3:L480,F3:F480,"<>Presidential*",H3:H480,"")


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/12/2020 2:55 PM, dpb wrote:

On 9/12/2020 2:26 PM, Claus Busch wrote:

Hi,

Am Sat, 12 Sep 2020 14:05:54 -0500 schrieb dpb:

    F        H    I    J    K    L
Presidential - Bks        0001    76018    100     82.99 >>> Presidential - Tuit        0001    76018    100     1,020.00
Presidential-border-any        0001    76018    100     642.00

That is, sum colum L if column F contains text beginning with
"Presidential" irrespective of other qualifiers...

try:
=SUMIF(F3:F480,"Presidential*",L3:L480)
or
=SUMPRODUCT((LEFT(F3:F480,12)="Presidential")*L3:L480)


Regards
Claus B.

"You dah man!" again, Claus!!  :)

=SUMIFS($L$5:$L$480,$F$5:$F$480,"Presidential*",$H$5:$H$480,"")

seems to do the trick...

OK, how to negate a condition?

Say wanted everything EXCEPT "Presidential*"

=SUMIF($F$5:$F$480,NOT("Presidential*"),$L$5:$L$480)

doesn't error, but doesn't return anything but zero, either.

Ended up using

=SUMIF($L$5:$L$480)-SUMIF($F$5:$F$480,"Presidential*",$L$5:$L$480)

that gets the answer but seems a kludge.

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/16/2020 7:47 PM, Claus Busch wrote:

Hi,

Am Wed, 16 Sep 2020 19:38:59 -0500 schrieb dpb:

OK, how to negate a condition?

Say wanted everything EXCEPT "Presidential*"

=SUMIF($F$5:$F$480,NOT("Presidential*"),$L$5:$L$480)

doesn't error, but doesn't return anything but zero, either.

Ended up using

=SUMIF($L$5:$L$480)-SUMIF($F$5:$F$480,"Presidential*",$L$5:$L$480)

try:
=SUMIFS(L3:L480,F3:F480,"<>Presidential*",H3:H480,"")

Oh, yeah...I think you had reminded me of using relational operators
before, Claus, and I got fixated on functionals and forgot...

Thanks...that does the trick (of course)

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)