Excel COUNTIFS Rules?

[bopper]

It looks like in order to use COUNTIFS, each range must be the same size. Is this true? Are there other rules associated with COUNTIFS? Will any other function work better?
Here's a condensed version of the sheet I'm building. The first two criteria is to get a date range. The last criteria is number of occurrences of a name. It works when applied to the HQ column only. The problem is when getting number of occurrences for multiple franchises.

# of HQ purchases, works: =COUNTIFS(F:F,">="&L8,F:F,"<="&L9,G:G,$L7)
# of franchise purchases, broken: =COUNTIFS(F:F,">="&L8,F:F,"<="&L9,H:I,$L7)

 

[bopper]

I found a workaround. It's not ideal and there's probably a better solution, but it works for now. Expansion will be annoying. I just calculated each franchise column and added them together.
=COUNTIFS(F:F,">="&L8,F:F,"<="&L9,H:H,$L7)+COUNTIFS(F:F,">="&L8,F:F,"<="&L9,I:I,$L7)

I tried to expand the date range to match the width of the franchise range, but that didn't seem to work... I may also put additional dates in the table so that wosn't really an option
=COUNTIFS(F:G,">="&L8,F:G,"<="&L9,H:I,$L7)

 

[Intel_man]

You can have a throw away row, either at the top, or at the bottom of your table that houses your COUNTIFS expression

So, if you have them above your table, you can have the "# HQ Purchases" reference the value output from G5 and "# Franchise purchase" can be achieved by using the SUM expression from H5:I5, or theoretically, you can set that to like H5:AA5 or somewhere out to space to the right.

That way, if you ever need to expand your table further with more franchise #'s (i.e. Franchise 3, Franchise 4, etc..) you can just expand to the right as you normally would, and then drag the COUNTIFS expression to the right, just ensure the L7, L8, L9 expressions have the $L$7 syntax to lock that cell in.

If you don't want a "throw away" row, you're best bet is to go down the VBA route to code that in the background.

 

[bopper]

You can have a throw away row, either at the top, or at the bottom of your table that houses your COUNTIFS expression

So, if you have them above your table, you can have the "# HQ Purchases" reference the value output from G5 and "# Franchise purchase" can be achieved by using the SUM expression from H5:I5, or theoretically, you can set that to like H5:AA5 or somewhere out to space to the right.

That way, if you ever need to expand your table further with more franchise #'s (i.e. Franchise 3, Franchise 4, etc..) you can just expand to the right as you normally would, and then drag the COUNTIFS expression to the right, just ensure the L7, L8, L9 expressions have the $L$7 syntax to lock that cell in.

If you don't want a "throw away" row, you're best bet is to go down the VBA route to code that in the background.

I think I get it. so the countifs will be above each franchise in row 5 and count the names for that franchise column only. The total number I'm looking for in L11 will be a sum of row 5.

I've never touched the coding stuff. I guess that's VBS. I've always found a way to do it with in-cell formulas. Is there an issue with sharing the excell file when I do that? I've always avoided using arrays... or whatever they're called, the ctrl+shift+enter things... for fear of the sheet not working when opened by someone else.

 

[Intel_man]

I think I get it. so the countifs will be above each franchise in row 5 and count the names for that franchise column only. The total number I'm looking for in L11 will be a sum of row 5.

The sum of row 5, from H5 onwards to the right, is for L12. L11 should just reference G5 unless you plan to have more than 1 column of HQ purchase.... which would complicate things.

Is there an issue with sharing the excell file when I do that?

If you code in VBA, the excel file format you'd have to use is .xlsm which when you send to others and they open it for the first time, it would typically disable the macro for "untrusted" files until they click allow. So there shouldn't be any issues.