# If and countif with two variables

#### rob737

##### Board Regular
Hi Forum
Hope all is well.

I need some help with a formula.

I have two worksheets

Data and Results.

In the results table I have a heat map and the formula matches and counts two variables from data.

Example

e.g.

Column A Column C
AAA 2020
AAA 2020
AAA 2050
BBB 2050
BBB 2010
BBB 5030

I used the formula =COUNTIF('Data'!\$J\$61:\$J\$77, "5050") # I do this for each value in Column A and Column C in my heat map

e.g.

formula =COUNTIF('Data'!\$J\$61:\$J\$77, "5050") # subsequent cells 5040,5030,5020,5010

The problem I have is by fixing the range every time I add a row to data I need to change the formula.

I cant seem to get something like =if(‘data’,”5030”countif)

Any help or preferably a better way would be much appreciated, I don’t use xl a great deal.

Many thanks
Regards
Rob

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

##### MrExcel MVP
Care to specify where you have the symbols like AAA in the sheet called Data?

#### rob737

##### Board Regular
Care to specify where you have the symbols like AAA in the sheet called Data?

The text values AAA,BBB,CCC are in column A. The comparison text is in column C 1010,1020,1030 in the data spreadsheet.

Eg

Column A Column C

AAA 2020
AAA 5030
AAA 3010
AAA 2020

BBB 4010
BBB 4010
BBB 2030
BBB 4010

As such I would like the following result into my report sheet

AAA Text

2020 = 2
5030 = 1
3010 = 1

BBB Text

4010 = 3
2030 = 1

At the moment I am specifying the number of rows for column A in the data sheet

COUNTIF('Data'!\$J\$61:\$J\$77, "5050") next cells 5040,5030,5020,5010
COUNTIF('Data'!\$J\$61:\$J\$77, "4050") next cells 4040,4030,4020,4010

And I do the same for 3050,3040.....

So the heat map is 5X5 1010 to 5050

The problem I have is I don't want to hard code the range in the data sheet (e.g. \$J\$61:\$J\$77,) because if I add a row say 78 I have to go through all of the other formulas and adjust them.

So basically

I want to count all the potential values in the 25 option range 1010 to 5050 for AAA, BBB, CCC etc. without hardcoding the rows.

Regards
Rob

##### MrExcel MVP
Is BBB and 2020 combination impossible?

Replies
0
Views
64
Replies
1
Views
102
Replies
3
Views
65
Replies
1
Views
48
Replies
0
Views
42