Adding based on multiple criteria

  • Thread starter Thread starter Custard Tart
  • Start date Start date
C

Custard Tart

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?
 
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
 
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.).
 
Hi,
Opps use this formula instead off the other I missread your post

sumproduct((C1=$A$1:$A$100)*($B$1:$B$100="Complete"))
 
Since OP is wanting to check multiple combinations of letters, need to add
criteria arrays.

=SUMPRODUCT(((A2:A100="GM")+(A2:A100="GMQ))*(B2:B100="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.
 
Back
Top