Hi All

Could anyone help wih this formula calculation

i am trying to establish whether a machine that i refurbished (has a 12
month warranty) has failed (been returned to me and logged) during that
warranty period, the data i have to work with is the serial number of the
equipment plus the date it was refurbished.

Therefore i would like to perform a serial number match over the last 12
months per month as all equipment is returned to me for repair.

Data i have is:

"With costs" worksheet, "Serial no" column and "Date" of the refurb column
(among others, but i figure i will only need these two columns for the
calculation)

The sheet i want to put the data onto has the 12 months of the year Jan -
Dec ("Monthly") so that i can see how many failures during Jan how many
during Feb etc

Hopefully someone can understand my bad explanation :)

Many thanks

Derek

Assuming that your dates are in column D of the With costs sheet,
occupying rows 1 to 18 (used in my test - adjust to suit your data),
and that your months are the first 3 characters of the month in A2:A13
of your monthly sheet, put this formula in B2 and copy down:

=SUMPRODUCT(--(TEXT('With costs'!D$2:D$18,"mmm")=A2))

If you use the full month name, then add a fourth "m" to "mmm".

Hope this helps.

Pete


On Sep 26, 4:30 pm, Derek wrote:
Hi All

Could anyone help wih this formula calculation

i am trying to establish whether a machine that i refurbished (has a 12
month warranty) has failed (been returned to me and logged) during that
warranty period, the data i have to work with is the serial number of the
equipment plus the date it was refurbished.

Therefore i would like to perform a serial number match over the last 12
months per month as all equipment is returned to me for repair.

Data i have is:

"With costs" worksheet, "Serial no" column and "Date" of the refurb column
(among others, but i figure i will only need these two columns for the
calculation)

The sheet i want to put the data onto has the 12 months of the year Jan -
Dec ("Monthly") so that i can see how many failures during Jan how many
during Feb etc

Hopefully someone can understand my bad explanation :)

Many thanks

Derek

Hi Pete

That works a treat, but what i really need is the serial number match i.e. a
particular machine serial number comes back to me, i put that in the coloumn
and it looks for a match over the previous 12 months to see if it has been
back before i.e under my warranty, sorry about my dodgy explanation

Thanks

Derek

"Pete_UK" wrote:

Assuming that your dates are in column D of the With costs sheet,
occupying rows 1 to 18 (used in my test - adjust to suit your data),
and that your months are the first 3 characters of the month in A2:A13
of your monthly sheet, put this formula in B2 and copy down:

=SUMPRODUCT(--(TEXT('With costs'!D$2:D$18,"mmm")=A2))

If you use the full month name, then add a fourth "m" to "mmm".

Hope this helps.

Pete


On Sep 26, 4:30 pm, Derek wrote:
Hi All

Could anyone help wih this formula calculation

i am trying to establish whether a machine that i refurbished (has a 12
month warranty) has failed (been returned to me and logged) during that
warranty period, the data i have to work with is the serial number of the
equipment plus the date it was refurbished.

Therefore i would like to perform a serial number match over the last 12
months per month as all equipment is returned to me for repair.

Data i have is:

"With costs" worksheet, "Serial no" column and "Date" of the refurb column
(among others, but i figure i will only need these two columns for the
calculation)

The sheet i want to put the data onto has the 12 months of the year Jan -
Dec ("Monthly") so that i can see how many failures during Jan how many
during Feb etc

Hopefully someone can understand my bad explanation :)

Many thanks

Derek