CODEWITHSUNDEEP
excelexcel-formula
user7393973
user7393973

Reputation: 2440

COUNTIFS with 2 different ranges and conditions

I have a table from which I need to count the ammount of specific numbers.

The table is dynamic and can have between 1 to 25 columns where the numbers are and then a code that starts with either a letter, 1 or 2. It has multiple rows too.

What I need is to have formulas to count the ammount of each specific number range if the code starts with the correct character as shown in the example image:

example

I can't manage to join the condition of the first range being between 2 numbers and the second range starting with a specific character.

The formula should look somewhat like this (count numbers between 200 and 299 with the code starting with 2):

=COUNTIFS(Table[[1]:[4]];">=200";Table[[1]:[4]];"<=299";Table[code];"2*")

letter  1   2       letter  1   2
100-199 200-299 200-299     2   1   1
300-399 400-499 400-499     3   3   3
500-599 600-699         2   2
700-799             2

1   2   3   4   code
139 307 165     B01
430             2CTE
581 703         PDC
312 354 528 746 GVM7
600 477         1OMC
299 425 413     2LP
231 666 420 433 1MLTQ

Upvotes: 0

Views: 234

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

put this in F3 then copy over and down:

=IFERROR(SUMPRODUCT((Table[[1]:[4]]>=--LEFT(B3,3))*(Table[[1]:[4]]<=--RIGHT(B3,3))*(((ISNUMBER(F$2))*(LEFT(Table[[code]:[code]])=F$2&""))+((NOT(ISNUMBER(F$2)))*(NOT(ISNUMBER(LEFT(Table[[code]:[code]]))))))),"")

enter image description here

As per your comments to get everything between 400 and 499 and starts with 2:

=SUMPRODUCT((Table[[1]:[4]]>=400)*(Table[[1]:[4]]<=499)*(LEFT(Table[[code]:[code]])="2"))

Upvotes: 2

Related Questions