countif (S)?

[WDB2]

I have a spreadsheet where the data is all on sheet two and I want to get counts onto sheet one.

I want to populate column E on sheet 1 if the criteria matches. I need to therefore enter my formula in column E on sheet one. It needs to say something along the lines of:

If column D on sheet 2 matches cell B2 on sheet 1 and if column F on sheet 2 contains "Sub" then count all items like this and return the value in cell E2. Ideally I also want to build in to say that column L on sheet 2 must have a date in the future as well.

 

[Aladin Akyurek]

I have a spreadsheet where the data is all on sheet two and I want to get counts onto sheet one.

I want to populate column E on sheet 1 if the criteria matches. I need to therefore enter my formula in column E on sheet one. It needs to say something along the lines of:

If column D on sheet 2 matches cell B2 on sheet 1 and if column F on sheet 2 contains "Sub" then count all items like this and return the value in cell E2. Ideally I also want to build in to say that column L on sheet 2 must have a date in the future as well.

On Excel 2007 and beyond...

=COUNTIFS(Sheet2!$D$2:$D$400,B2,Sheet2!$F$2:$F$400,"*Sub*")

With a date test:

=COUNTIFS(Sheet2!$D$2:$D$400,B2,Sheet2!$F$2:$F$400,"*Sub*",
    Sheet2!$L$2:$L$400,"4-Oct-11")

On all versions...

=SUMPRODUCT(--(Sheet2!$D$2:$D$400=B2),
    --ISNUMBER(SEARCH("Sub",Sheet2!$F$2:$F$400))
 
=SUMPRODUCT(--(Sheet2!$D$2:$D$400=B2),
    --ISNUMBER(SEARCH("Sub",Sheet2!$F$2:$F$400),
    --(Sheet2!$L$2:$L$400="4-Oct-11"+0))