Counting Col A contents if Col B returns a dedicated value.

[BlueDingo]

Good Afternoon (AEST) Aust,

Searching for a formula for the following:

Fields A1:A100 are filled with 4 specific dates (one is eg, 01-Aug-12) and Fields B1:B100 filled with YES or NO.

I need to conform to Excel 2003, to add the number of "01-Aug-12" if Col B returns a "YES".

Tried some of the formulas in the forum but not much luck, hence Excel 2003.

 

[sutto]

Good Afternoon (AEST) Aust,

Searching for a formula for the following:

Fields A1:A100 are filled with 4 specific dates (one is eg, 01-Aug-12) and Fields B1:B100 filled with YES or NO.

I need to conform to Excel 2003, to add the number of "01-Aug-12" if Col B returns a "YES".

Tried some of the formulas in the forum but not much luck, hence Excel 2003.

sumproduct will do the trick.

This is assuming that the date you want to ADD is in cell 'D1'.

=SUMPRODUCT(--(A1:A100=D1),--(B1:B100="Yes"))

Note that you can't use whole column refs with sumproduct (so no A:A).

 

[Sal Paradise]

=SUMPRODUCT((A1:A100="Date1")*(B1:B100="Yes"))+SUMPRODUCT((A1:A100="Date2")*(B1:B100="Yes"))+SUMPRODUCT((A1:A100="Date3")*(B1:B100="Yes"))+SUMPRODUCT((A1:A100="Date4")*(B1:B100="Yes"))

Change Date1 Date2 Date3 Date4 to whatever the dates you are looking for are.

 

[BlueDingo]

For Sutto:

The dates I need to add appear in column A from row 1 through to row 100, not in cell D1.
I need to add how many have the date "01-Aug-12" and so forth.

For Sal paradise:

These need to be a sum for each date, not one total number by adding all dates together.

Thanks to you both.

 

[Sal Paradise]

Then just remove the + in my formula and make each separate sumproduct its own cell. No biggie.

 

[BlueDingo]

Sorry Sal, this is still not working for me.

=SUMPRODUCT((F$8:F$100="01-Oct-12")*(I$8:I$100="Yes")) Column i has two "yes" against "01-Oct-12" and its still returning zero.

 

[Sal Paradise]

Chances are your dates are recorded as dates. In which case you can do one of two things:

1) Enter the number corresponding to that date.
2) Enter the date the old-fashioned way:

Excel 2003
	A	B	C
1	Date	Number	Date Formula
2	2012/10/1	41183	2012/10/1
Sheet1

Cell Formulas
Range	Formula
B2	=TEXT(A2,"@")
C2	=DATE(2012,10,1)

So either:

1) =SUMPRODUCT((F$8:F$100=41183)*(I$8:I$100="Yes"))
2) =SUMPRODUCT((F$8:F$100=DATE(2012,10,1))*(I$8:I$100="Yes"))

 

[BlueDingo]

Hello Sal, gee you're patient with this, I admit I'm a bit of a clutz with formulas, but here goes,

Col F Col G Col i
[TABLE="width: 176"]
<TBODY>[TR]
[TD="class: xl27, width: 78, bgcolor: yellow"]Sub
Date
[/TD]
[TD="class: xl27, width: 92, bgcolor: yellow"]PAR sent
[/TD]
[TD="class: xl26, width: 64, bgcolor: yellow"]Complete
Y/N
[/TD]
[/TR]
[TR]
[TD="class: xl29, width: 78, bgcolor: transparent"]01-Oct-12
[/TD]
[TD="class: xl28, bgcolor: transparent"]03-Aug-12
[/TD]
[TD="class: xl31, width: 64"]YES
[/TD]
[/TR]
[TR]
[TD="class: xl29, width: 78, bgcolor: transparent"]01-Sep-12
[/TD]
[TD="class: xl28, bgcolor: transparent"]30-Aug-12
[/TD]
[TD="class: xl31, width: 64"]YES
[/TD]
[/TR]
[TR]
[TD="class: xl29, width: 78, bgcolor: transparent"]01-Oct-12
[/TD]
[TD="class: xl28, bgcolor: transparent"]01-Aug-12
[/TD]
[TD="class: xl31, width: 64"]YES
[/TD]
[/TR]
[TR]
[TD="class: xl32, width: 78, bgcolor: transparent"]01-Dec-12
[/TD]
[TD="class: xl28, bgcolor: transparent"][/TD]
[TD="class: xl31, width: 64"]NO
[/TD]
[/TR]
[TR]
[TD="class: xl28, bgcolor: transparent"]01-Dec-12
[/TD]
[TD="class: xl28, bgcolor: transparent"]25-Nov-12
[/TD]
[TD="class: xl31, width: 64"]YES
[/TD]
[/TR]
[TR]
[TD="class: xl29, width: 78, bgcolor: transparent"]01-Nov-12
[/TD]
[TD="class: xl28, bgcolor: transparent"]20-Oct-12
[/TD]
[TD="class: xl31, width: 64"]YES
[/TD]
[/TR]
</TBODY>[/TABLE]



Therefore in the chart below, Col F should reveal:
SEP = 1
OCT = 2
SEP = 1
NOV = 1

Col E Col F Col G
[TABLE="width: 328"]
<TBODY>[TR]
[TD="class: xl29, width: 188, bgcolor: #ccffcc"]Sub for:
[/TD]
[TD="class: xl26, width: 78, bgcolor: #ccffcc"][/TD]
[TD="class: xl30, width: 78, bgcolor: #ccffcc"]Comp per mth:
[/TD]
[TD="class: xl32, width: 92, bgcolor: #ccffcc"]% Comp
[/TD]
[/TR]
[TR]
[TD="class: xl27, bgcolor: #ccffcc"]SEP=
[/TD]
[TD="class: xl28, bgcolor: #ccffcc"]2
[/TD]
[TD="class: xl33, bgcolor: transparent"]0
[/TD]
[TD="class: xl31, bgcolor: #ccffcc"]0%
[/TD]
[/TR]
[TR]
[TD="class: xl27, bgcolor: #ccffcc"]OCT=
[/TD]
[TD="class: xl28, bgcolor: #ccffcc"]25
[/TD]
[TD="class: xl33, bgcolor: transparent"]0
[/TD]
[TD="class: xl31, bgcolor: #ccffcc"]0%
[/TD]
[/TR]
[TR]
[TD="class: xl27, bgcolor: #ccffcc"]NOV=
[/TD]
[TD="class: xl28, bgcolor: #ccffcc"]26
[/TD]
[TD="class: xl33, bgcolor: transparent"]0
[/TD]
[TD="class: xl31, bgcolor: #ccffcc"]0%
[/TD]
[/TR]
[TR]
[TD="class: xl27, bgcolor: #ccffcc"]DEC=
[/TD]
[TD="class: xl28, bgcolor: #ccffcc"]22
[/TD]
[TD="class: xl33, bgcolor: transparent"]0
[/TD]
[TD="class: xl31, bgcolor: #ccffcc"]0%
[/TD]
[/TR]
</TBODY>[/TABLE]


Regards, Pete (Private MSG was accidently deleted)

 

[Sal Paradise]

Okay, that is a totally different question since you are looking for any date within a month...

In that case, you should use:
=SUMPRODUCT((F$8:F$100>=DATE(2012,10,1))*(F$8:F$100<DATE(2012,11,1))*(I$8:I$100="Yes"))

That will return the number of dates after October 1st, but before November 1st (all days in October).