How to use COUNTIF only if certain conditions are met

[Douglas Laing]

In my spreadsheet has a list of classes and the type of classes that they are and how long each class lasts.  I have 4 columns. 

YES      Class Title                            Class Type                 #  of periods

    x       Excel Formulas                     Excel                                2

             Formatting spreadsheets     Excel                               1

            Merging in Word                  Word                              1

I place an x in column A if I like the class.  So as you see above, the first class has an x.

On another sheet in the workbook  I have this set up

                                 Excel                 Word                   Power Point

# of classes               

Total class hours

How do I get it to put a 1 (for the 1 Excel class I chose) in the first row and a 2 in the second row, since that this the number of periods of the class?  There will be several Excel, Word, and Power Point classes (these are not the actual class types) on the first spreadsheet.  Remember I only want to count the class if there is an X in column A.  So if I have several Excel classes with an X in column A it has give me the sum of my x's for that type of class.  I know how to do it if I just want a to tally the number of classes in each category, but not just the ones with a x.  I also don't know how to get that # of periods for either all the classes per category or just the ones with an x.

[#2]

Hi Douglas

what you are looking for is sumifs and countifs which let you create multiple sets of conditions that need to be matched before anything is added.

so you might have something like sumifs(D1:D3,A1:A3,"x",C1:C3,"Excel") which would add up the numbers in column D where A has an x and C is Excel..

The other, and probably better way to do this is with a pivot table.

I've uploaded a workbook with both solutions for you to have a look at.

(I do hope this isn't your Excel Formulas homework :p )

[#3]

Thank you Philip.  Not homework, but real work.  I made it work with the formula.  Your pivot table just added all the classes x or not.  There is one more item.  I thought that I could figure it out with the information you gave me, but no.  There is another column - Times taught.  I need to multiply that number with the Class length column.  Can I do this on the fly or do I need to have a hidden column that does this and pick up the number from that column?  I've attached the worksheet with the new column.

[#4 · Accepted solution]

Hia,

If I understand the question then I think the attached solves it.

The extra formula is an array so needs to be entered with ctrl+shift+enter.