SumIFs Formula issue

[PFS12]

[TABLE="width: 1124"]
<colgroup><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Salesman #[/TD]
[TD]Responsible[/TD]
[TD]Vendor / Invoice #:[/TD]
[TD]Description:[/TD]
[TD]Date:[/TD]
[TD]Average Cost[/TD]
[TD]Price:[/TD]
[TD]Vendor #[/TD]
[TD]Sell Price:[/TD]
[/TR]
[TR]
[TD]NWO[/TD]
[TD]NWO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$100.00[/TD]
[/TR]
[TR]
[TD]219[/TD]
[TD]NWO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$100.00[/TD]
[/TR]
[TR]
[TD]NWO[/TD]
[TD]219[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$100.00[/TD]
[/TR]
</tbody>[/TABLE]


I have a large table that I am sure I can clean up in many regards, but currently I am trying to simplify the above into one formula.

The one I am currently using is;
=SUMIFS(November!$L$19:$L$2013,November!$D$19:$D$2013,"NWO")+SUMIFS(November!$L$19:$L$2013,November!$E$19:$E$2013,"NWO")

Its on a separate page on a different chart, I plot this data monthly so this is an example for November. This formula works great with one exception, When both columns are populated with "NWO" is doubles that value, so the above example shoudl total 300, but excel shows it as 400.

How can I eliminate the double count? Or how can I write a formula that sums the sales column, if either salesperson or responsible salesperson contains NWO. (Very rarely will the data contain the same value, but I don't want to have to go back and sort line by line each month)

I know there is a cleaner solution, I just can't find it! Thanks in advanced.

 

[Nine Zero]

try this out

=SUMIFS(November!$L$19:$L$2013,OR(November!$D$19:$D$2013,November!$E$19:$E$2013),"NWO")

 

[Nine Zero]

sorry my bad, wrong answer on my end

you might have to place a helper column for this situation and just hide it

so maybe after your sell price column M try this

=OR(November!$D$19:$D$2013="NWO",November!$E$19:$E$2013="NWO")

then in your cell wher eyou had the sumifs.... do a sumif

=SUMIF(M:M,"TRUE",L:L)

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(November!$L$19:$L$2013,(November!$D$19:$D$2013="NWO")+(November!$E$19:$E$2013="NWO"))


Note that when D and E both are NWO, L will be doubled for that record.

 

[Nine Zero]

Note that when D and E both are NWO, L will be doubled for that record.

I think that's what OP is trying to avoid though... but good solution to take care of both situations

 

[PFS12]

I could, but I have multiple salesmen that I am tracking, so this is just an example of one person, so I'd have to write multiple "helper" columns. there has to be a way to do this, I was thinking the answer might be in a SUMPRODUCT formula, but I am not certain, that's why I am here asking. I'd love a SUMIFS/OR but not BOTH formula...

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF((November!$D$19:$D$2013="NWO")+(November!$E$19:$E$2013="NWO"),November!$L$19:$L$2013))

 

[PFS12]

Control+shift+enter, not just enter:

=SUM(IF((November!$D$19:$D$2013="NWO")+(November!$E$19:$E$2013="NWO"),November!$L$19:$L$2013))

Please explain?

It Definitely works, I'm just wondering why. Thank you for the help

 

[Aladin Akyurek]

Please explain?

It Definitely works, I'm just wondering why. Thank you for the help

IF((November!$D$19:$D$2013="NWO")+(November!$E$19:$E$2013="NWO"),November!$L$19:$L$2013)

summarizes...

IF((November!$D19="NWO")+(November!$E19="NWO"),November!$L19)
IF((November!$D20="NWO")+(November!$E20="NWO"),November!$L20)
…

TRANSLATION:
When D19="NWO" and E19="NWO" >> TRUE + TRUE >> 2, outer IF yields L19 for 2, that is, non-zero, means TRUE >> L19
When D19="NWO" and E19="NWO" >> TRUE + FALSE >> 1, outer IF yields L19 for 1, that is, non-zero, means TRUE >> L19
When D19="NWO" and E19="NWO" >> FALSE + FALSE >> 0, outer IF yields L19 for 0, that is, zero, means FALSE >> FALSE


Outer SUM sums L numbers which IF deliver.


Hope this helps.