More help with SumProduct

Hello everyone,

XLD was kind enough to help me with a Sumproduct problem a while ago. I am now trying to do an inspection profiling table where I think I need to use the same function again, but am having trouble adapting the formula to a different problem, and need more help.

Basically in the attached sanitised workbook, I am generating a series of inspection dates for 'NORATE and UNRATE' inspection types in columns AA:BJ.

CU2:CX18 contains a table, in which Column CW should have all the 'NORATE and UNRATE' inspections profiled for any given month that fall in the year defined by the dates in CW3 and CW4, ie between 01/04/2009 and 31/03/2010.

Please look at my formula in CW6 which is supposed to show all the inspections that fall in January between 01/04/2009 and 31/03/2010. I have also defined a named range which is being used in the formula. I don't know whether there is a problem with the formula because of the way I am nesting the MONTH and AND functions within SUMPRODUCT or if the error is in the way I have defined the named range.

Any advice would be much appreciated.

Is this what you need?

Code:

---

=SUMPRODUCT(--(TEXT(NoRate_UnRate_Inspection_Dates,"mmmm")=$CU6),
                    --(NoRate_UnRate_Inspection_Dates>$CW$3),
                    --(NoRate_UnRate_Inspection_Dates<$CW$4))

---

Thanks xld,

I tried your formula and the value in CW6 evaluates to 0. I know that there is at least 1 instance of an inspection in January 2010 because of the date in AA28.

Could it be an error in my defined name range? I did a lot of fiddling around with that and am not confident I've defined it properly as don't fully understand about offsetting

I forgot to mention that your dynamic range definition point to offset 36 column, it should be 25, but this still gives 0 for January.

Where is the item that should be counted?

The dates to be counted are in columns AA to BJ. Just glancing at the data visually shows at least AA28, AB11, AC10 should be included in the January 2010 count.

Ach! I see it now.

I was mis-reading the dynamic range formula, I thought you were starting at A6, but you are starting at AA6.

Change that formula to

Code:

---

=OFFSET(Inspections!$AA$6,,,COUNTA(Inspections!$AA:$AA)-6,36)

---

and you should be good.

Thank you very much xld - that seems to have solved it.