Adding based on multiple criteria
 

My spreadsheet has two colums side by side.

Column A: contains letters such as GM, GMA, GMQ etc.
Column B: contains complete, partial, postponed/cancelled.

At the bottom, there is a TOTAL box.

I need to be able to total up multiple letters in Column A (for
example, only GM and GMQ) which are complete ONLY.

How would I do this?

Adding based on multiple criteria
 

Hi,
Let's say in cell C1 you enter GM so in D1 enter

=sumproduct(--(C1=$A$1:$A$100),$B$1:$B$100)

change range to fit your needs but remember range has to be the same in both
parts of the formula

if this helps please click yes, thanks

"Custard Tart" wrote:

My spreadsheet has two colums side by side.

Column A: contains letters such as GM, GMA, GMQ etc.
Column B: contains complete, partial, postponed/cancelled.

At the bottom, there is a TOTAL box.

I need to be able to total up multiple letters in Column A (for
example, only GM and GMQ) which are complete ONLY.

How would I do this?

Adding based on multiple criteria
 

Thanks for the answer but I'm still a little confused as to how that
formula is composed (i.e. what are the 100s for, etc.).

Adding based on multiple criteria
 

Hi,
A1:A100 is the range where it supposed to be the information in your case GM
or GMQ

"Custard Tart" wrote:

Thanks for the answer but I'm still a little confused as to how that
formula is composed (i.e. what are the 100s for, etc.).

Adding based on multiple criteria
 

Hi,
Opps use this formula instead off the other I missread your post

sumproduct((C1=$A$1:$A$100)*($B$1:$B$100="Complete "))

"Eduardo" wrote:

Hi,
A1:A100 is the range where it supposed to be the information in your case GM
or GMQ

"Custard Tart" wrote:

Thanks for the answer but I'm still a little confused as to how that
formula is composed (i.e. what are the 100s for, etc.).

Adding based on multiple criteria
 

Since OP is wanting to check multiple combinations of letters, need to add
criteria arrays.

=SUMPRODUCT(((A2:A100="GM")+(A2:A100="GMQ))*(B2:B1 00="Complete"))

Adjust range sizes as needed, but make sure they are same size. Feel free to
replace text critiera with cell references. Also, can not callout entire
columns (A:A) in SUMPRODUCT unless using XL 2007.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Eduardo" wrote:

Hi,
Let's say in cell C1 you enter GM so in D1 enter

=sumproduct(--(C1=$A$1:$A$100),$B$1:$B$100)

change range to fit your needs but remember range has to be the same in both
parts of the formula

if this helps please click yes, thanks

"Custard Tart" wrote:

My spreadsheet has two colums side by side.

Column A: contains letters such as GM, GMA, GMQ etc.
Column B: contains complete, partial, postponed/cancelled.

At the bottom, there is a TOTAL box.

I need to be able to total up multiple letters in Column A (for
example, only GM and GMQ) which are complete ONLY.

How would I do this?