Help with SUMIF function criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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<>"Michael"))
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.
 
=SUMPRODUCT(--ISNA(MATCH('Master Client List-do not
edit'!D3:D65536,{"Jason",Michael"},0)),'Master
Client List-do not edit'!I3:I65536)
 
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.
 
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).
Click to expand...


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

=SUM(IF((B16:B20<>"Michael")*(B16:B20<>"Jason"),C16: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
 
Back
Top