multiple conditions

  • Thread starter Thread starter Alicia
  • Start date Start date
A

Alicia

I'm sure this question is already in here somewhere, but
here goes.

I have 2 workbooks. Book 1 has attendance numbers for
several classes in several cities. Book 2 reports total
monthly attendance for each class using SUMIF.

Problem: I need to break up this report so it calculates
attendance for the classes in individual cities. Right
now, it counts and sums all the cells that correspond to
English 101 (for example), but I want it to sum all the
cells that correspond to English 101 in New York, or
English 101 in DC, individually.

Let's say Book 1 is set up this way
Column A = course name
Column B = city
Column C = # attended

and Book 2, Sheet "New York" looks like this
Column A = course name
Column B = SUMIF('Book 1'!$1:$200,A1,'Book 1'!$C$1:$C$200)

I know this is very convoluted, but basically, I need to
do how to sum or count based on multiple conditions.
 
Hi Alicia,

'[Book 2.xlx]New York'!B2, copied down:

=SUMPRODUCT(--('[Book 1.xls]Sheet1'!A$2:A$10=A2),--('[Book
1.xls]Sheet1'!$B$2:$B$10="New York"),'[Book 1.xls]Sheet1'!$C$2:$C$10)

Hope this helps!
 
Hi Alicia,
For English 101 in New York use =SUMPRODUCT(--(A1:A100="English
101"),--(B1:B100="New York"),C1:C100)
Double negation with first two to coerce Boolean to 0/1; Also the
parentheses are needed as shown.
Note: you cannot use A:A with SUMPRODUCT

You may also wish to try to make a Pivot Table - see Help and come back with
questions

Best wishes
 
Thanks, guys! Both your answers helped a lot. Now, another similar issue: does this method work for COUNTIF?

Each class scheduled shows up as a different entry, so I need to count each "English 101" entry in New York.

Thanks again :)
 
Just remove C1:C100


=SUMPRODUCT(--(A1:A100="English 101"),--(B1:B100="New York"))

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


Alicia said:
Thanks, guys! Both your answers helped a lot. Now, another similar
Click to expand...
issue: does this method work for COUNTIF?
Each class scheduled shows up as a different entry, so I need to count
Click to expand...
each "English 101" entry in New York.
 
Back
Top