SUMPRODUCT with Date Range

  • Thread starter Thread starter LouD
  • Start date Start date
L

LouD

Hello,

I have been searching this forum all day and I can't seem to get any code
that I find to work. Below is what I believe should work. Cell D349 has
3/1/2007 and cell D350 has 3/31/2007.

=SUMPRODUCT(--(B2:B347>=--D349),--(B2:B347<=--D350),--(C2:R347="39BD382*"))

What I'm basically lookinf for is: if the part number 39BD8382 is present
anywhere in cell range C2:R347, how many shipped in March of 2007?

Thanks in advance.
 
If they are proper dates all you need is

=SUMPRODUCT(--(B2:B347>=D349),--(B2:B347<=D350),--(ISNUMBER(SEARCH("39BD382",C2:R347))))
 
Bob,

I get a #VALUE! error in the cell with the formula. I am supposed to hit
ctrl + shift + enter when I'm done editing the formula to put the { } around
it, right? And how do I check for proper dates? I know I have the cells
formatted as dates...
 
Firstly, apologies, I failed to spot the third criteria was multi-column.
Try

=SUMPRODUCT((B2:B347>=D349)*(B2:B347<=D350)*(ISNUMBER(SEARCH("39BD382",C2:R347))))

Secondly, no, it is not array entered, just a standard formula.
 
Bob,

This formula works perfect:

=SUMPRODUCT((B2:B347>=D349)*(B2:B347<=D350)*(ISNUMBER(SEARCH("39BD382",C2:R347))))

Thanks for all your help.
 
Back
Top