Need help on formula with multiple conditions...

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...

Try SUMPRODUCT()

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

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)?

Same formula but remove the last condition

so it would be:

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

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)

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

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?

One way

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


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"DiDi" <[email protected]> wrote in message
news:[email protected]...
>
> 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?
>
>
> --
> DiDi
> ------------------------------------------------------------------------
> DiDi's Profile:
> http://www.excelforum.com/member.php...o&userid=33473
> View this thread: http://www.excelforum.com/showthread...hreadid=532772
>

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

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

=SUMPRODUCT(--('Closed IT Jobs'!$D$2:$D$75=$B15),--('Closed IT 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. I would like the answer to be 0. I tried an =IF and maybe I'm not setting it up right, but I'm getting an error.

Any and all help will be appreciated.

Try this:

=IF(E15=0,0,insert_your_formula_here)

Didn't work ... but thanks.