Need help on formula with multiple conditions...

[DiDi]

I know this can be done ... but I'm not exactly sure how. Let's say we
have 3 columns.

Column A contains names which are not unique.
Column B contains a code to indicate, in this case a source for the
data.
Column C has the data that needs to be summed...

I have used =sumif(A1:A50,"Joe Smith",C1:C50) - but how do I get it to
add only Joe Smith's cells in column C when the letter E is in column
B?

It's driving me crazy...

 

[Cutter]

Try SUMPRODUCT()

=SUMPRODUCT(--(A1:A50="Joe Smith"),--(B1:B50="E"),(C1:C50)

 

[DiDi]

This worked.... You can't imagine how crazy I've been going over this.
It brings up another question regarding multiple conditions.

How can I count up the total number of times that Joe Smith (A) is
using the E code (B)?

 

[Cutter]

Same formula but remove the last condition

so it would be:

=SUMPRODUCT(--(A1:A50="Joe Smith"),--(B1:B50="E")

 

[DiDi]

That does it .... and to think I wasted a bunch of time trying all sorts
of different fixes.... very, very much appreciated.
(I'm not a big fan of smilies.....but)

 

[Cutter]

You're very welcome. You have a lovely smile(s).

 

[DiDi]

You are a kind person.... but I hate to bother you again, but what if I
want to find out how many times the entire Smith family (A) has used
code E (B)?

I tried using "Smith*" and I get nothing... does this mean that
wildcards don't work with this formula or am I overlooking something?

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(LEFT(A2:A20,5)="Smith"),--(B2:B20="E"))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[DiDi]

Worked like a charm .... thanks to all who helped. I am in your debt

 

[DiDi]

Spreadsheet is almost done, everything looks great but - here's th
formula so far....

=SUMPRODUCT(--('Closed IT Jobs'!$D$2:$D$75=$B15),--('Closed I
Jobs'!$E$2:$E$75="I"),('Closed IT Jobs'!$F$2:$F$75))/E15

How do I get rid of the "DIV/0!" error when cell E15 contains a zero.
would like the answer to be 0. I tried an =IF and maybe I'm not settin
it up right, but I'm getting an error.

Any and all help will be appreciated

 

[Cutter]

Try this:

=IF(E15=0,0,insert_your_formula_here

 

[DiDi]

Didn't work ... but thanks.