SUMIFS question

[Eric Garcia]

I am trying to use sumifs to gather information if one of two tests
are true. I would like to be able to make a purchase apear as
executed if Employee 1 (Column N) or Employee 2 (Column O) has
selected Executed from a multiple option list on a form I am exporting
to Excel. I have tried using the OR function, but it won't sum a
logical function, just give me true or false. I know how to do this
if I create an extra hidden line in the table, but I would prefer to
not have to do that. If I were to ask excel to make this calculation
I would say to it "Add the price/cost of all purchases for this
ACCOUNT, match them with the ITEMS that match this line item, and
place it in the Exectued Column if either Employee 1 (Column N) or
Employee 2 (Column O) selects EXECTUED. I will list the code I have
below replaceing cell names with the terms i used above. The problem
with this code is both employees have to select executed, and I want
only on of the two people to have to select executed.

=SUMIFS(Price/Cost(Column L),Column E,Account,Column C,Item,Employee1
(Column N),Executed,Employee 2(Column O),Executed)

I apologize if this is confusing, or if I am missing a very simple
fix. I am not yet an Excel Ninja, so I am looking to those who are to
give me some guidance on my path to Enlightenment

 

[Bob Phillips]

Try

=SUMPRODUCT(--(Account="Item"),
--((Employee1=="Executed")+(Employee 2="Executed)),
Price/Cost(Column L))

 

[Shane Devenshire]

Hi,

If I understand your question you would need to use the following SUMIFS
formula

=SUMIFS(L$1:L$6,C$1:C$6,"Item",E$1:E$6,"Account",O$1:O$6,"Executed")+SUMIFS(L$1:L$6,C$1:C$6,"Item",E$1:E$6,"Account",N$1:N$6,"Executed")

This is one formula but requires two sumifs. The SUMPRODUCT formula would
look like this:

=SUMPRODUCT(--(C$1:C$6="Item"),--(E$1:E$6="Account"),--(N$1:N$6="Executed")--(O$1:O$6="Executed"),L$1:L$6)

if you want to reference the entire columns these formula become:

=SUMIFS(L:L,C:C,"Item",E:E,"Account",O:O,"Executed")+SUMIFS(L:L,C:C,"Item",E:E,"Account",N:N,"Executed")

=SUMPRODUCT(--(C:C="Item"),--(E:E="Account"),--(N:N="Executed")--(O:O="Executed"),L:L)

 

[Shane Devenshire]

Hi,

Here is a second approach:

Set up a criteria area that looks something like this (I used the range S1:V3

ITEM# Account# EMp1 EMP2
item Account Executed
item Account Executed

Then use the formula

=DSUM(C1:O7,L1,S1:V3)

Or the full column equivalent:

=DSUM(C:O,L1,S1:V3)

This approach assumes you have titles at the tops of the columns C:O which
match those shown at the top of the criteria area above.