Regarding multiple instances of countifs in a formula

Copper Contributor

Hello :)

 

I'm trying to create a formula, where I have to put multiple instances of countifs in.

 

It currently looks like this:

 

=TÆL.HVISER('Raw data'!D2:D1001;"League";'Raw data'!G2:G1001;"W")

 

I need to add another criteria called "FNM" - Can it be done? :)

4 Replies

@NovaNiller 

Absolutely! You can add another criteria called "FNM" to your formula using the SUM function and another instance of TÆL.HVISER. Here's how:

Modified formula:

=SUM(TÆL.HVISER('Raw data'!D2:D1001;"League";'Raw data'!G2:G1001;"W"),TÆL.HVISER('Raw data'!D2:D1001;"League";'Raw data'!G2:G1001;"FNM"))

This formula works by:

  1. Calling TÆL.HVISER twice: Each instance counts the occurrences where the first range ("Raw data'!D2:D1001") equals "League" while the second range ("Raw data'!G2:G1001") matches either "W" or "FNM".
  2. Wrapping both calls in SUM: This adds the results of both TÆL.HVISER calls, giving you the total count for both "W" and "FNM" matches in the "League" column.

Make sure you enter this formula as an array formula by pressing Ctrl+Shift+Enter instead of just Enter. This is required for formulas containing SUM and multiple ranges.

Here are some additional notes:

  • You can add even more criteria by adding more instances of TÆL.HVISER within the SUM function, each with its own specific criteria in the second range.
  • If you want to count separately for "W" and "FNM", you can use nested IF statements within each TÆL.HVISER call instead of relying on SUM.

I hope this helps

Hello and thank you for the quick answer :)

If I understand your suggestion to the formula correct, then I need to switch it around - I need to count all "W"'s, wether it's "League" or "FNM" :)

@NovaNiller 

If you want to use a single COUNTIFS formula with multiple criteria, you can do it in your language.

In this formula, ANTALHVISER is the Danish equivalent of COUNTIFS. The syntax is similar to the English version. The formula counts the cells in the specified ranges where the corresponding cells meet the specified criteria.

Your original formula was correct for counting cells with the criteria "League" and "W" in the specified ranges. If you want to add the "FNM" criteria, you can use this modified formula:

=ANTALHVISER('Raw data'!D2:D1001; "League"; 'Raw data'!G2:G1001; "W") + ANTALHVISER('Raw data'!D2:D1001; "FNM"; 'Raw data'!G2:G1001; "W")

 

This formula counts the cells where the criteria are "League" and "W", and then adds to that count the cells where the criteria are "FNM" and "W". If this is what you need, then your original formula structure was indeed correct.The text and the steps with the help of AI.

 

My answers are voluntary and without guarantee!

 

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

@NovaNiller You can do this with SUM and COUNTIFS using a static array { } to input multiple OR criteria into a single argument. Please see the attached example file, which should automatically adjust to work with the regional and language settings on your system...