SUMIFS & COUNTIFS with 2 Criteria

J Smith

I'm trying to work on COUNTIFS first. It seems if I can crack one the other is pretty close.

The riddle:

=COUNTIF({Post Repair Count}, ({Domestic/Asian}, "Domestic"))

I've tried COUNTIFS with the 2nd range but seems that the criteria for range 1 is tripping me up.

{Post Repair Count} has no criteria to it. Just need to count it if criteria in range 2 are met @ROW from source sheet. This will be very similar to the SUMIFS that will follow.

Hope I am making sense. Thank you in advance

Paul Newcome

If there is no criteria to a range, then really you have no need to count it. What you are really counting is the number of "Domestic" cells.

=COUNTIFS({Domestic/Asian"}, @cell = "Domestic")

To sum a number column based on the above range/criteria set, it would look something like this:

=SUMIFS({Range to sum}, {Domestic/Asian"}, @cell = "Domestic")

J Smith

Paul, thank you for reaching out.

In this case there is a reason for counting {Post Repair Count} because I need to know how many invoices are within that range that are "Domestic"

Does that make sense?

Thank you sir

Jeremy

Paul Newcome

Are there any rows that will have "Domestic" but will be blank in the Post Repair Count column?

J Smith

Yes sir. In fact, the {Post Repair Count} is referencing multiple columns from my master sheet. Some of the referenced cells have figures and others don't.

Thank you for looking at this.

Paul Newcome

How many columns does the {Domestic/Asian} range cover?

J Smith

{Domestic/Asian} is one column. The {Post Repair Count} References 4 columns.

My goal is to count the cells that have numbers present in {Post Repair Count}, the criteria to count would be {Domestic/Asian} = Domestic @ROW.

However, I think I have found a work around! I can accomplish this in a report then use it on a dashboard.😎

Paul Newcome

Yes. Reports may be an easier option. One of the problems with working this through formulas is that your ranges have to match in size and shape. One range referencing 4 columns and another range referencing a single column (both within the same function) will throw an error. You would have to write out 4 separate COUNTIFS (one for each of the post repair count columns) and then add them all together.