sum of rows that meet certain criteria

[trqwrench]

In a worksheet I am building I have a column called "Cancelled" that uses an "X" to show that the information in the row is cancelled. Another column called "Sent to PM" is used to record a date. What formula should I use to indicate when there is both an "X" in the "Cancelled" column and a date in the "Sent to PM" column for the same row? I want to display the number of rows wherein this condition occurs - in a single cell.

[TMS]

Sounds like you need COUNTIFS.

Regards, TMS

[trqwrench]

Bingo, Mr. Shuttleworth. I tried COUNTIFS once but it didn't work either. Apparently I didn't do something correctly, because when I tried again it worked perfectly.

Nice of you to respond from clear over across the pond. Hope England has a nice Christmas.

Thank you.

[MarvinP]

Hi trqwrench and welcome to the forum.

Your formula could look like:
=SUMIFS(C:C,A:A,"x",B:B,">0")

See the attached example and look at: http://blog.contextures.com/archives...iple-criteria/

[trqwrench]

Thank you, Marvin P for your prompt response. SUMIFS didn't work however COUNTIFS does. The formula I used is: =COUNTIFS(C11:C42,"=X",M11:M42,">0"). I created a log for my section at work

I kept tinkering with COUNTIF, then I found the COUNTIFS; it works perfectly.

Thanks for the pointer.

[MarvinP]

I thought you wanted the sum because of the title of the thread.

I'm glad you found an answer to your problem.

[TMS]

You're welcome.

Hope England has a nice Christmas

Yes, let's hope. It's kinda cold, wet and miserable at the moment but there's time for it to improve, one way or another.

All the best to you all over there for Christmas and the New Year.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.