countif 3 matches found for only last 5 rows

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
471
Office Version
  1. 365
Platform
  1. Windows
Hi would anyone have a formula for counif with multi instances,
The formula would be in cell E1, and would match cells G1,H1 and i1
I have attached imige for better understanding
In the example the word apple,mark and number 1, is only matched once in the last 5 rows.
Thanks
Capture111.PNG
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
sorry made a mistake and can not edit this post, so please ignore, thanks
Could a moderater please delete this post if possible
 
Upvote 0
Hello, I tried solving your Query. I hope this could help. But will be glad if some better way can be suggested by other experts. Firstly your data is not dynamic, so I have made a table for your data, to make the range dynamic, and other than that I have used the countifs formula as per your requirement.
Book2 (version 1).xlsb
ABCDEFGH
1itemtypenumber2applemark1
2applemark1
3orangeterry12
4pearstephen12
5applemark1
6pearterry12
7orangestephen12
8applemark12
9applemark12
10applemark1
11orangeterry12
12orangestephen14
13orangemark14
14pearmark14
15pearmark14
16applemark1
17applemark1
18applemark
Sheet1
Cell Formulas
RangeFormula
E1E1=COUNTIFS(INDEX(Table1[item],ROWS(Table1[item])-4):INDEX(Table1[item],ROWS(Table1[item])),F1,INDEX(Table1[type],ROWS(Table1[type])-4):INDEX(Table1[type],ROWS(Table1[type])),G1,INDEX(Table1[number],ROWS(Table1[number])-4):INDEX(Table1[number],ROWS(Table1[number])),H1)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top