SUMIF with Mutiple Ranges & Criteria

[PokerZan]

I'm doing a sales report and have a single sheet with all my call/result metrics on it.

I want to use SUMIF (I think ) to calculate the cells in a particular column if a cell in a specific column has a specific text entry as well as a different text entry in another column (the two columns are region and district). I know how do do a single range/criteria in a SUMIF statement, but I don't know how to add multiple SUMIF range/criterias.

So basically here is what I am looking for:

Sum a particular column G if column F equals "East Enterprise" and column V equals "CSI".

I am currently breaking these out in a pivot table and adding a seperate sheet to the report, then doing a sumif statement on the columns I need but I know there has to be a way to do it in a single formula, it is just beyond my grasp.

Also, if this makes no sense whatsoever, FLAME away... that just may be the movitation I need today.

Humbly,

PokerZan

[Bob Phillips]

=SUMPRODUCT(--(F2:F100="East Enterprise"),--(V2:v100="CSI"),G2:G100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"PokerZan" <PokerZan.1t91uo_1123175121.4106@excelforum-nospam.com> wrote in
message news:PokerZan.1t91uo_1123175121.4106@excelforum-nospam.com...
>
> I'm doing a sales report and have a single sheet with all my call/result
> metrics on it.
>
> I want to use SUMIF (I think ) to calculate the cells in a
> particular column if a cell in a specific column has a specific text
> entry as well as a different text entry in another column (the two
> columns are region and district). I know how do do a single
> range/criteria in a SUMIF statement, but I don't know how to add
> multiple SUMIF range/criterias.
>
> So basically here is what I am looking for:
>
> Sum a particular column G if column F equals "East Enterprise" and
> column V equals "CSI".
>
> I am currently breaking these out in a pivot table and adding a
> seperate sheet to the report, then doing a sumif statement on the
> columns I need but I know there has to be a way to do it in a single
> formula, it is just beyond my grasp.
>
> Also, if this makes no sense whatsoever, FLAME away... that just may be
> the movitation I need today.
>
> Humbly,
>
> PokerZan
>
>
> --
> PokerZan
> ------------------------------------------------------------------------
> PokerZan's Profile:
http://www.excelforum.com/member.php...o&userid=23480
> View this thread: http://www.excelforum.com/showthread...hreadid=392980
>

[PokerZan]

Thanks Bob,

It did the trick!

One quick question though, what do the "--" do before the parentheses do?

PZan

[PokerZan]

And the questions never stop... :P

There are about 7 different options that could be in column V, I am only looking to extract "CSI", the others I want to add up the other 6 options...

So is there an way to add up all that DON'T say "CSI" in column V?

Thanks,
PZan

[Bob Phillips]

Yeah, straight-forward

=SUMPRODUCT(--(F2:F100="East Enterprise"),--(V2:v100<>"CSI"),G2:G100)

for the low-down on --, take a look at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"PokerZan" <PokerZan.1t9a6w_1123185952.1391@excelforum-nospam.com> wrote in
message news:PokerZan.1t9a6w_1123185952.1391@excelforum-nospam.com...
>
> And the questions never stop... :P
>
> There are about 7 different options that could be in column V, I am
> only looking to extract "CSI", the others I want to add up the other 6
> options...
>
> So is there an way to add up all that DON'T say "CSI" in column V?
>
> Thanks,
> PZan
>
>
> --
> PokerZan
> ------------------------------------------------------------------------
> PokerZan's Profile:
http://www.excelforum.com/member.php...o&userid=23480
> View this thread: http://www.excelforum.com/showthread...hreadid=392980
>

[PokerZan]

Wow, great site, thanks a ton!