SUMIF

2 posts / 0 new
Quick reply
Last post
Tue, 10/13/2015 - 11:12
#1
SUMIF

HI,

Sorry, i'm new to this. I hope i'm posting on the right site. I have tried several things and my SUMIF formula is not working. Basically i'll explain what i'm trying to do as briefly as I can.

I have a list of fellows that are given a stipend of $500 each to spend for one whole year. I keep track of this on an excel spreadsheet, in one column i have the name, next column the amount they spent, next column the description (not important) and finally i designate the last column with PDF(for their stipend) in this column you will only see two words either PDF or DEPT. In other words if the expense isn't coming out of their stipend then we designate it with the word dept. What i need to do is calculate how much each fellow has spent of their pdf(stipend amount) excluding anything in the PDF column that is worded dept.

I need something like Jane $190.00 PDF (this will be calculated) if it said Jane $510 DEPT. I cannot include this.

I was using this formula =SUMIF(Fellows!A:A,"Jane",Fellows!B:B) which works great because it tells me how much each fellow spent but it gives me their total pdf and dept expenses combined. I only want to see their pdf expenses. Few things worth mentioning, Columns A-Fellows, Amount-B, etc...and Fellows is the name of the worksheet. One more thing, a sample is below but in one year each fellow will appear multiple times, in other words, Jane might appear ten times in one year with 10 different types of expenses that are either expensed to her PDF or the dept. I only need to know how much pdf expenses Jane had in one year.

Fellow AMOUNT DESCRIPTION PDF

Jane 190.00 Book PDF
Joe 53.33 Paper DEPT
Lucy 50.00 Book DEPT
Chad 93.60 Coffee PDF

I hope this makes sense.

Thanks,

Sam

Top
Sat, 10/17/2015 - 10:36
Try the SUMIFS function to solve your problem.

Hi Sam

Thanks for your post.

In this example, you can use the SUMIFS function, which allows you to use multiple criteria in your formula.

The formula you are looking for is this:

=SUMIFS(B10:B16,A10:A16,"Jane",D10:D16,"PDF")

This formula breaks down as follows:

  • B10:B16 contains the cells we want to add up.
  • A10:A16 contains the cells we want to check against the first criterion
  • "Jane" is the first criterion
  • D10:D16 is the range we want to check against the second criterion
  • "PDF" is the second criterion.

Note that the SUMIFS function allows you to add up to 127 range/criteria pairs.

For more information, check out our lesson on the SUMIFS function and how to use it. You might also find our lesson on COUNTIFS useful. COUNTIFS allows you to count the rows that match the criteria set provided in the function.

Regards

David

Top

Add new comment