Rules for Multiple Criteria SUMIFS/COUNTIFS and Arrays

The solution given is for a specific example: I really want to know what the general rule is when building sumifs/countifs on combinations of multiple criteria on multiple columns. What works, what deposit and when to use ; or , .

Perhaps this can help. If you aren't aware of it comma separators in arrays are understood to be column separators, and semi-colons are understood to be row separators. When presented column to column there is an implicit AND criteria. When presented "cross-wise" or transposed column to row there is an implicit OR criteria.

One way to see this in action would be to highlight / select the SUMIFS part in the formula bar in each of the formulae and press the F9 function key. You will see the arrayed results in each case. This has helped me to follow the logic.

Another way would be to make the formula in question the active cell, click on Formulas, click Evaluate Formula in the Formula Auditing group and then click Evaluate repeatedly in the dialogue window. Watch the "magic".

Has this helped?

Glad it helped, cmbh. Thank you for the feedback and the rep.