COUNTIFS/SUMIFS for multiple column criterias

sahilhq

Hi,

I have been trying to find the answer to my question on other Smartsheet community posts but no luck so far hence this question - how do I SUMIF/COUNTIF when there are multiple conditions to look at? Basically, I am trying to find all instances of the value "2 - OPPORTUNITY FOR IMPROVEMENT" in a column when the Site Audit Conducted By column has my name "Sahil Haque". Can you please suggest with a formula to enter in my formula sheet which will reference the data from my master sheet? Based on the screenshot shown below, the formula result should be 2 since my name is shown twice when the criteria "2 - OPPORTUNITY FOR IMPROVEMENT" occurs. Thanks in advance for your help!

Paul Newcome

Ok. So you want to count the number of instances which means you want to use a COUNTIFS.

=COUNTIFS(1st_criteria_range, 1st_criteria, 2nd_criteria_range, 2nd_criteria)

Paul Newcome

You would need a SUMIFS or COUNTIFS with the S on the end to include multiple range/criteria sets.

=SUMIFS(range_to_sum, 1st_criteria_range, 1st_criteria, 2nd_criteria_range, 2nd_criteria)

=COUNTIFS(1st_criteria_range, 1st_criteria, 2nd_criteria_range, 2nd_criteria)

sahilhq

@Paul Newcome Thanks for the response again. Could it be possible to merge these two formulas into one? Also, can you please specify what this formula would be for my scenario?

Paul Newcome

Do you want a COUNTIFS or a SUMIFS? Are you trying to count instances or are you trying to add numbers based on specific criteria?

sahilhq

@Paul Newcome Could be Countifs or Sumifs. I am trying to count instances when "2 - OPPORTUNITY FOR IMPROVEMENT" occurs when the site audit is conducted by me ("Sahil Haque").

So in the e.g. below, the number of instances when "2 - OPPORTUNITY FOR IMPROVEMENT" occurs is twice and the site audit conducted by "Sahil Haque" for this categoery occurs twice, therefore the total value needs to show as 2. Thanks for your help mate!

Paul Newcome

Ok. So you want to count the number of instances which means you want to use a COUNTIFS.

=COUNTIFS(1st_criteria_range, 1st_criteria, 2nd_criteria_range, 2nd_criteria)

sahilhq

@Paul Newcome Many thanks Paul. For some strange reason, I was trying to overcomplicate the solution/formula in my head. Didn't realise it was so straightforward!

Paul Newcome

No worries. There have been PLENTY of times where I have made things much more complicated than they needed to be. Happy to help. 👍️