Countif by month and part and distinct values

[Greymud]

Excel 2016, windows 10 pro. They won't let me unzip xl2bb to use it, so i guess it's story time.

We heat treat parts and need to track completed batches to predict how many of each need to be completed. The part I'm stuck on is counting batches per part for a month.

The columns I'm using are on Worksheet 'ALD1'.
Column A is date ran
Column B is Load #
Column E is Part Family (this is an hlookup from a table if that matters)

When I build these, I try to build each formula separately before I put them together. This way I can narrow down what doesn't work. That may be my issue, as well.

Column A. Trying to filter dates by month, in Worksheet 'Test'. I have Data Validation in D1 to select a month.
=SUMPRODUCT(1*(MONTH('ALD1'!A:A)=Test!D1))
returns #VALUE!
Possible issue - My drop down list is the month spelled out and the actual dates are formatted like 2023-01-06

Column E. Counting just one part type
=COUNTIF('ALD1'!E:E,"*Input*")
This appears to work. I didn't actually count 276 items, but tested it on a smaller data set.

Column B. Occasionally, we'll get multiple samples from one load, I need to count distinct values.
{=SUM(1/COUNTIF('ALD1'!B:B,'ALD1'!B:B))}
returns #DIV/0!
Possible issue - Some of the load numbers are <previous row>+1. I could easily retrain this away if needed.

The last part is what order to combine these parts. I was assuming filter by date, then remove duplicates, then count part groups.

Any assistance is very appreciated. Hopefully, I'm using proper verbiage.

 

[AFPathfinder]

My first thought would be to just create a pivot table; Part Family in "Rows" and Load # in "Values". Then I'd have to do a little playing around to figure out how to subtotal the date by months.

However, I understand some folks' apprehension with using pivot tables. Pivot tables aside, I would add an extra column on ALD1 sheet and put a formula in that column that gives you the month value of the run date in column A; like:

=MONTH(A2)

Then you could just use an easy SUMIFS to fish your data, such as:

=SUMIFS('ALD1'!B:B,'ALD1'!D:D,$A$2)

where "$A$2" is your data validation cell containing your month.

Instead of using the month names in the data validation cell list, you could just use numbers 1-12 and format the data validation cell as "Mmmm" to get it to look like the month name. That will allow your SUMIFS function to match the MONTH function values in column D.

When it comes to SUMPRODUCT and array formulas, I don't know them well enough to spot errors without being able to play with them. Even then, I sometimes throw the white flag and go back to pivot tables or VBA.

Hope this helps!

 

[Greymud]

Thanks!

I'll see if a pivot table will work.for us. I'll also look at a helper column to see if that fixes my problem.