SUMIF() with AND() function??

[Ann Scharpf]

I apologize if this question has already been answered. I
am having trouble searching for this in the archives
because the search is interpreting the word AND as a
boolean operator instead of allowing me to do a search
that looks for references to the AND() worksheet function.

Is it possible to include an AND() function inside a SUMIF
() function? Here is my stab at what I was trying to do:

=SUMIF(IF(AND(System="difms-c",LaborType="maintenance")
=TRUE),Reg)

System, LaborType and Reg are all named ranges.

I want to total the value of the Reg (hours) when both the
System and LaborType meet specific conditions. The only
way I have figured out to make this work is to add another
column that contains just the AND() statement and then I
do the SUMIF on that new column and add up the numbers
when the new column is TRUE. There are several different
AND() type combinations I need to get SUMIF() totals for,
though and it hardly seems elegant to have all these
extraneous columns.

Is there a more straightforward way to do this? I'd
appreciate any help you can give me.

Thanks.

Ann Scharpf

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(System="difms-c"),--(LaborType="maintenance"),Reg)

assuming you want to check the 2 first ranges for your criteria and when
TRUE sum the 3rd

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Harlan Grove]

I apologize if this question has already been answered. I
am having trouble searching for this in the archives
because the search is interpreting the word AND as a
boolean operator instead of allowing me to do a search
that looks for references to the AND() worksheet function.

Use Google, not Microsoft's execrable newsgroup interface.

http://www.google.com/groups?as_epq=SUMIF with AND&as_ugroup=*excel*

Is it possible to include an AND() function inside a SUMIF()
function? Here is my stab at what I was trying to do:

...

No. The first and optional third arguments to SUMIF must be references to single
area 2D ranges in open workbooks. Nothing else works. The second, criteria
argument must evaluate to a string that shows a single criteria expression.

=SUMIF(IF(AND(System="difms-c",LaborType="maintenance")
=TRUE),Reg)

...

One way,

=SUMPRODUCT(--(System="difms-c"),--(LaborType="maintenance"),Reg)

 

[Ann Scharpf]

Thanks, Peo, I will try that. I did look in the help
under SUMPRODUCT() but I didn't get the impression that it
would help me.

I am assuming you mean the dashes to be in the formula.
What do those mean?

Thanks again.

Ann

-----Original Message-----
One way

=SUMPRODUCT(--(System="difms-c"),-- (LaborType="maintenance"),Reg)
assuming you want to check the 2 first ranges for your

criteria and when TRUE sum the 3rd.

 

[firefytr]

Hi,

You may want to check out SUMPRODUCT. It may go something like this:

=SUMPRODUCT(--(System="difms-c"),--(LaborType="maintenance"),Reg

 

[CLR]

Just as an alternative, you can concatenate your "System" and "Labot Type"
columns into a third column and then do a regular SUMIF using that column as
the criteria.................you may find other uses for the concatenated
column later also........

Vaya con Dios,
Chuck, CABGx3

 

[Norman Harker]

Hi Ann!

Re:
I am assuming you mean the dashes to be in the formula.
What do those mean?

They are double negatives used to coerce returns of 1 and 0 from the
immediately following implicit IF expressions that would otherwise
return TRUE or FALSE.

 

[Ron H]

You're referring to the unary operators.
Essentially the "double minus" coerces TRUE into -1 and then 1.
A FALSE remains 0.
Do a search for "unary" operators on the newsgroup.
There was one good discussion at
http://tinyurl.com/3d6tk

Ron Hekier

 

[Ann Scharpf]

Thanks for pointing me in the right direction, Peo & Ron!

It was a lot of reading and I made a BUNCH of stupid
mistakes in trying to apply this ... but I finally got it!

This is very cool!

Ann