If and countif with two variables

[rob737]

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

 

[Aladin Akyurek]

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

 

[rob737]

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

Hi Aladin

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.

Thanks for your help.

Regards
Rob

 

[Aladin Akyurek]

Is BBB and 2020 combination impossible?