HOWTO: Use COUNTIF with SUMPRODUCT/OFFSET by MONTH to calculate number of part used by Part Number?

[AGYANI]

Happy New Year 2024 ! Dear Chandoo Gurus ! I am struggling to find the total number of parts used by month in a matrix format[columns H to T] from the raw data in columns A thru F

Appreciate your ideas and help using the COUNTIF/SUMPRODUCT/OFFSET by month, or any other technique.

Thank you in advance

 

[Monty]

Hello Yani
As per my understanding!

=SUMIFS(INDEX($H$2:$T$1000, , MATCH(TEXT(A2,"mmm"), $H$1:$T$1, 0)), $A$2:$A$1000, ">="&DATE(YEAR(A2), MONTH(A2), 1), $A$2:$A$1000, "<="&EOMONTH(A2,0))

Please adjust the range accordingly

 

[Debaser]

Try this - in I2:
=SUMPRODUCT(($B$2:$F$1569=TEXT($H2,"00"))*(TEXT($A$2:$A$1569,"mmm")=I$1))
and copy across and down.

 

[pecoflyer]

Protected view??

 

[p45cal]

While @Debaser 's formula returns the same as your expected results, be aware that there are some part numbers preceded by a space, eg. 02 in cells C38 and C757 which two values are not included.
So a tweak:

=SUMPRODUCT((TRIM($B$2:$F$1569)=TEXT($H2,"00"))*(TEXT($A$2:$A$1569,"mmm")=I$1))

 

[AGYANI]

Thankyou Monty, dbaser, pecoflyer and p45cal GURUS! , I will try all your suggestions tomorrow and let you know. I am sure I am in good hands with all your suggestion, Thanks to Chandoo. Amazing team you have.

 

[AGYANI]

=SUMIFS(INDEX($H$2:$T$1000, , MATCH(TEXT(A2,"mmm"), $H$1:$T$1, 0)), $A$2:$A$1000, ">="&DATE(YEAR(A2), MONTH(A2), 1), $A$2:$A$1000, "<="&EOMONTH(A2,0))

I tested you formula and adjusted the range for your formula I dont see how you are counting the "Part #" in the range from B2:F1569? I get a #VALUE error

 

[AGYANI]

Try this - in I2:
=SUMPRODUCT(($B$2:$F$1569=TEXT($H2,"00"))*(TEXT($A$2:$A$1569,"mmm")=I$1))
and copy across and down.

I tried your formula and entered it in I2 as per your instruction, and copy across and down , see attachment

 

[AGYANI]

While @Debaser 's formula returns the same as your expected results, be aware that there are some part numbers preceded by a space, eg. 02 in cells C38 and C757 which two values are not included.
So a tweak:

=SUMPRODUCT((TRIM($B$2:$F$1569)=TEXT($H2,"00"))*(TEXT($A$2:$A$1569,"mmm")=I$1))

@p45cal I did the same thing here is the result, looks like your numbers are looking like what I expect, I will manually validate this again, looks like TRIM did the trick , or convert it in to INT for equal.

 

[AGYANI]

Tried my formula also slight variation seems the numbers are validated
=SUMPRODUCT((MONTH($A$2:$A$1569)=MONTH(1&M$1))*COUNTIF(OFFSET($B$1:$F$1,ROW($B$2:$F$1569)-ROW($B$1),0),$J11))

 

[Monty]

I tested you formula and adjusted the range for your formula I dont see how you are counting the "Part #" in the range from B2:F1569? I get a #VALUE error View attachment 86033
View attachment 86032

Hello Agyani

please provide more details so I can assist you better.

 

[AGYANI]

Hello Agyani

please provide more details so I can assist you better.

Thank you Monty for getting back appreciate your willingness and kindness, I am attaching the file again with a comment on Cell I2 , please read it I think I tried to explain as far as I could articulate my requirement, TIA.