SUMIF based on text in adjacent cell

**kevinjay1** · 01-07-2013, 03:20 PM

Hi, I am trying to count and sum numbers in other cells if column B has a specific text.

In my sheet, I have three distinct texts in column B - "On time", "No call" and "Untimely". My sheet is used to track deliveries to a customer and I get weekly reports in a different sheet which I then copy and paste into my sheet. I would like to have each load that is delivered on time summed for an annual average, same for no call and untimely. I am trying to use a SUMIF function, but am having problems getting it to add the totals up properly.

The 2 formulas that I have tried are =SUMIF(B:B,"=on time",C7:N153) which ends up counting each occurrence of "On time" that gives me a result of 48 (number of weeks on sheet). The other formula =SUM(IF(B:B,"=on time",C7:N153)) counts all deliveries and I get a result of 463 (total number of loads).

Help?

**Moo the Dog** · 01-07-2013, 03:47 PM

Try to match range sizes whenever possible. I would try this:

=SUMIF(B7:B153,"On Time",C7:C153)

=SUMIF(B7:B153,"No call",C7:C153)

=SUMIF(B7:B153,"Untimely",C7:C153)

**Moo the Dog** · 01-07-2013, 03:56 PM

I just had a thought... if you are trying to add cells from a multi-column range (Columns C:N) then my first post won't work for you... if you need to sum columns C:N based on column B, try either one of these formulas:

REGULAR formula:

Formula:

Please Login or Register  to view this content.

ARRAY Formula (Applied using Ctrl + Shift + Enter):

Formula:

Please Login or Register  to view this content.

- Moo

**kevinjay1** · 01-07-2013, 04:12 PM

Thanks a bunch Moo, before you replied I did your first suggestion but came back with "0" for results. I finally added the totals in cells C:N in column O and used my formula with O only and it worked. I also plugged in your regular formula and it came back very nicely. Thanks for replying and have a nice day