Home

COUNTIF Formula Help

New Contributor

Hello,

I am fairly new to working with Excel so please forgive me if this is a silly question. I have a set of data values in B2:G20. Let's say in row B20:G20 I have 2 values that match the values in the data set. How can I create a reference formula to count the number of values that appear ( Number of Hits column) in the row that match with the data set? 

 

 

Screen Shot 2019-01-06 at 2.48.11 PM.png

 

Screen Shot 2019-01-06 at 2.50.11 PM.png

 

 

 

I created the following but realized that I need a formula for a value in the data set that changes frequently.

=COUNTIF(B20:G20,"=48") + COUNTIF(B20:G20,"=43") + COUNTIF(B20:G20,"=24") + COUNTIF(B20:G20,"=8") + COUNTIF(B20:G20,"=17")

 

Thank you for any assistance!

2 Replies
Highlighted
=SUMPRODUCT(COUNTIF(B20:G20,{8,17,24,43,48})) 

 

 

 

NOTE: hope that it is not used  to check lottery result.

Highlighted
Thank you! Looks like I need to dig deeper into understanding arrays. Also no worries about lotto results, losing money is not my thing lol
Related Conversations
Need some help figuring out edge://sync-internals/
HotCakeX in Discussions on
0 Replies
Notifications from Planner in Teams
nwehl in Microsoft Teams on
0 Replies
Embedded Sharepoint List - Custom Forms not loading
sbayer in Microsoft Teams on
0 Replies
SharePoint 2013 LIst Filters (by latest date)
dmphil in SharePoint on
0 Replies
Adding Wiki Tab Issue
goatscandrum in Microsoft Teams on
1 Replies