Help with SUMIF function criteria
 

I am trying to sum column I based on the rows that do not have either Jason
or Michael in column D.

=SUMIF('Master Client List-do not edit'!D:D,"<Jason""<Michael",'Master
Client List-do not edit'!I3:I65535)

However, the above formula is giving me the results number of unique #rows
not equal to Michael and #rows not equal to Jason (i.e. all rows because it
sees it as an OR rather than an AND).

What is the correct formula?

Marina

I think SUMIF will only deal with one condition.
Try
=SUMPRODUCT((A1:A20000<"Jason")*(A1:A20000<"Mich ael"))
Adjust to suit, note that SUMPRODUCT will not work with whole columns (A:A)
etc, it has to be a specified range like A1:A36000, the same range of rows
must be used in all columns. This is well documented in help, post back if
you have a problem,
Regards,
Alan.
"Marina" wrote in message
...
I am trying to sum column I based on the rows that do not have either Jason
or Michael in column D.

=SUMIF('Master Client List-do not edit'!D:D,"<Jason""<Michael",'Master
Client List-do not edit'!I3:I65535)

However, the above formula is giving me the results number of unique #rows
not equal to Michael and #rows not equal to Jason (i.e. all rows because
it
sees it as an OR rather than an AND).

What is the correct formula?

Marina

=SUMPRODUCT(--ISNA(MATCH('Master Client List-do not
edit'!D3:D65536,{"Jason",Michael"},0)),'Master
Client List-do not edit'!I3:I65536)

Marina wrote:
I am trying to sum column I based on the rows that do not have either Jason
or Michael in column D.

=SUMIF('Master Client List-do not edit'!D:D,"<Jason""<Michael",'Master
Client List-do not edit'!I3:I65535)

However, the above formula is giving me the results number of unique #rows
not equal to Michael and #rows not equal to Jason (i.e. all rows because it
sees it as an OR rather than an AND).

What is the correct formula?

Marina

Just out of curiosity, why such a long name for an Excel Worksheet? Seems
it could make for a very long formula, sometimes daunting to users.

--
Regards,
Zack Barresse, aka firefytr


"Marina" wrote in message
...
I am trying to sum column I based on the rows that do not have either Jason
or Michael in column D.

=SUMIF('Master Client List-do not edit'!D:D,"<Jason""<Michael",'Master
Client List-do not edit'!I3:I65535)

However, the above formula is giving me the results number of unique #rows
not equal to Michael and #rows not equal to Jason (i.e. all rows because
it
sees it as an OR rather than an AND).

What is the correct formula?

Marina

"Marina" ha scritto nel messaggio


I am trying to sum column I based on the rows that do not have either
Jason or Michael in column D.

=SUMIF('Master Client List-do not
edit'!D:D,"<Jason""<Michael",'Master Client List-do not
edit'!I3:I65535)

However, the above formula is giving me the results number of unique
#rows not equal to Michael and #rows not equal to Jason (i.e. all
rows because it sees it as an OR rather than an AND).


Try this array formula (you have to press *not* just ENTER, *but*
CTRL+ALT+ENTER)

=SUM(IF((B16:B20<"Michael")*(B16:B20<"Jason"),C1 6:C20,0))

where I suppose B16:B20 is the range with names and C16:C20 is the range to
sum.

Hoping to be helpful...

Regards

--
Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------