matching dates and data - SUMPRODUCT?

**sav1979** · 05-15-2011, 02:01 PM

Hi all,

From the attachment I am trying to figure out what formula I need to use to do the following:

Match Column B with Column K so that I can see the mode of trans on each date or ideally within a range of dates. For example, I would like to see how many AF between 1st Feb and 28th Feb and so on.

Many thanks in advance for your assistance.

**TMS** · 05-15-2011, 02:32 PM

=SUMPRODUCT(($K$6:$K$43="AF")*($B$6:$B$43>=DATE(2011,2,1))*($B$6:$B$43<=DATE(2011,2,28)))

Regards

**Cutter** · 05-15-2011, 03:02 PM

COUNTIFS() is preferable to SUMPRODUCT()

=COUNTIFS($K$6:$K$43,"AF",$B$6:$B$43,">="&DATE(2011,2,1),$B$6:$B$43,"<="&DATE(2011,2,28))

and the DATE() portions could (and should) be replaced with cell references holding the dates

**sav1979** · 05-15-2011, 04:31 PM

Thanks guys, I went with the COUNTIF, works perfectly. Thanks for your time and efforts

**Cutter** · 05-15-2011, 05:21 PM

You're welcome. Thanks for the "scales tap".

**TMS** · 05-15-2011, 06:56 PM

Ditto. I'm struggling to break with the SUMPRODUCT Tradition (took a while to learn that).

I realised that COUNTIFS would be better in 2007 but Cutter had already beaten me to it. Cheers