Excel Formula to Count The Number Of Duplicate Values While Ignoring Blank Cells

JEF13

New Member
Joined
Jun 30, 2018
Messages
49
Office Version
  1. 2019
I have created an Excel spreadsheet that determined the number of weighted plates needed for a barbell when lifting a certain amount of weight.

As an example, if the weight to lift is 200lbs, a 45lb plate, 25lb plate, and a 5 2.5lb plate is needed.

The spreadsheet lists the weighted plates in a row. Using our 200lb example there would be 4 columns with a number in it: 45 25 5 2.5

The "45 25 5 2.5" indicates that a 45lb plate, 25lb plate, 5lb plate, and 2.5lb plate

Each value is in its own column.

I have created a formula to put all the value in one column with wording. The output if this column is:

45x1 35x0 25x1 10x0 5x1 2.5x1 1.25x0

The formula I am using is: ="45x"&COUNTIF(B5:H5,45)&" 35x"&COUNTIF(B5:H5,35)&" 25x"&COUNTIF(B5:H5,25)&" 10x"&COUNTIF(B5:H5,10)&" 5x"&COUNTIF(B5:H5,5)&" 2.5x"&COUNTIF(B5:H5,2.5)&" 1.25x"&COUNTIF(B5:H5,1.25)

My question is how can I ONLY display the values that are greater than 0. In this case, the output would look like:
45x1 25x1 5x1 2.5x1

I have included an image of the spreadsheet for visual reference.

Thanks in advance for your help.

Jeffrey
 

Attachments

  • Plate Calculator.png
    Plate Calculator.png
    7.1 KB · Views: 7

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Does the direction of the weight matter?

This will do what you ask for, but it will be in reverse order, i.e. it 1.25 first, 45 last

=TEXTJOIN(" ",1,IF(COUNTIF($B$5:$H$5,$B$2:$H$2),$B$2:$H$2&"x"&COUNTIF($B$5:$H$5,$B$2:$H$2),""))

Array confirmed with Ctrl Shift Enter
 
Upvote 0
This is exactly what is needed. Thank you!

I have one more question.

There are three formulas in total. I am wondering if there is a way to combine everything to fit into one cell which would allow each exercise to have its own weight plate calculator.

The formulas are:

Formula 1 (Weight Per Side Of The Dumbbell): =(B2-45)/2
Formula 2 (Plates Needed): =INDEX($B$1:$H$1,1,MATCH($B3,$B$1:$H$1,1)) in the first cell and the remaining cells =IF(($B3-SUM($B4:B4))>0,INDEX($B$1:$H$1,1,MATCH($B3-SUM($B4:B4),$B$1:$H$1,1)),"")
Formula 3 (jasonb75's formula): =TEXTJOIN(" ",1,IF(COUNTIF($B$5:$H$5,$B$2:$H$2),$B$2:$H$2&"x"&COUNTIF($B$5:$H$5,$B$2:$H$2),""))

I have uploaded a picture "Formulas.png" which shows the formulas (some formulas are truncated in the picture)

I have uploaded another picture "Workout Sheet.png" which is on a different tab in Excel.

It would be great to have Column D displays which plates are needed for each set.

As an example, column D3 has the output for plates needed for 200lbs in set 1. In set 2, column D4 has the output of plates needed for 220lbs. In set 3, column D5 has the output of plates needed for 230lbs.

How could this be done?

Thanks.

Jeffrey
 

Attachments

  • Formulas.png
    Formulas.png
    12.1 KB · Views: 4
  • Workout Sheet.png
    Workout Sheet.png
    4.6 KB · Views: 5
Upvote 0
While it might be possible to combine all of the formulas into 1 cell, in terms of practicality, it would resemble having a weigh bench or treadmill in the pool.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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