Summing ranges based on two (or more) conditions/criteria

If you are using Excel 2007, the try using the SUMIFS functions.

If using a prior version, use the IF function.

Why don't you edit your profile so that we know which version you are using?

Can do

Originally Posted by NBVC

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Well I hope you don't take this the wrong way but what should I put for a title? I'm having problems with a formula that I need help with. Not sure what else to title it. How is it such a bad title?

Originally Posted by NBVC

Try something like"

Thank you.

Originally Posted by NBVC

Thanks... now... I am not quite sure what your original question really is...

Do you essentially want 2 sumifs, one that sums the "Y" and one that Sums the "N"... and then multiply each by a different fixed value?

Can you elaborate a bit?

Basically It's a tax calculation. I have a column called PST. If I put a Y in that cell I want it to calculate the tax based on the data in the cell beside it. If I put an N I don't want it to calculate anything really, but I just want it to show up as 0. With the formula I'm using now, The "Y" part of it works. But for instance say I put all "N"s for each line item (saying I don't want to charge tax on that item). When I do that it doesn't do the calculations (which is right) but it leaves the cell blank. I would prefer the cell to show $0.00. Would it help if I uploaded the worksheet? I'm probably not explaining this too well.

Originally Posted by NBVC

Yes I think that would help... and put in some expected results...

OK. Here it is. The cell in question is H46. Basically if there is a Y in Column J, I want it to calculate the tax at 8% based on Column H (Extended Price). However if there are all N's in Column J, I want H46 to show as $0.00 and not just a blank cell. And alternatively, if you have a better way to do this, that would be great.

Originally Posted by NBVC

Well, if I understand it correctly, you could use formula:

=IF(COUNTIF(J19:J140,"Y")=0,0,SUMIF(J19:J40,"Y",H19:H40)*0.08)

which would return 0 if there are no "N"'s in column J...

However, you currently have your sheet set to not show 0's through Tools|Options|View tab...

If you want to see a zero in that cell, you need to turn that option back on... then you can format the cells in H19:H40 and other ranges through, Format|Cells >> Custom: $#,##0.00;;

Thank you. Worked perfectly.