ExcelBanter - Modify A SumProduct Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Modify A SumProduct Formula (https://www.excelbanter.com/excel-worksheet-functions/30912-modify-sumproduct-formula.html)

carl

Modify A SumProduct Formula

I currently use this formula:

=SUMPRODUCT(--($S$10:$S$60001=$H$2);$H$10:$H$60001)/$T$4

My ColS values are dates that look like so:

20050512 (year,month,day).

I would like to modify the formula to sum only if the 5th and 6th character
= 05.

Thank you in advance.

Duke Carey

Try:

=SUMPRODUCT(--(MID($S$10:$S$60001,5,2)="05");$H$10:$H$60001)/$T$4

"carl" wrote:

I currently use this formula:

=SUMPRODUCT(--($S$10:$S$60001=$H$2);$H$10:$H$60001)/$T$4

My ColS values are dates that look like so:

20050512 (year,month,day).

I would like to modify the formula to sum only if the 5th and 6th character
= 05.

Thank you in advance.

Bob Phillips

=SUMPRODUCT(--(MONTH($S$10:$S$60001)=5);$H$10:$H$60001)/$T$4

--
HTH

Bob Phillips

"Duke Carey" wrote in message
...
Try:

=SUMPRODUCT(--(MID($S$10:$S$60001,5,2)="05");$H$10:$H$60001)/$T$4

"carl" wrote:

I currently use this formula:

=SUMPRODUCT(--($S$10:$S$60001=$H$2);$H$10:$H$60001)/$T$4

My ColS values are dates that look like so:

20050512 (year,month,day).

I would like to modify the formula to sum only if the 5th and 6th
character
= 05.

Thank you in advance.

All times are GMT +1. The time now is 10:45 PM.