formula help

[kboy22vitt]

Hi, I'm new to formulas and new to the site. I have been using some video training in hope of getting the info I require. But at the moment I'm struggling to find any videos that actually are in line with what I am trying to do. Please bare with me as I'm a beginner. I have managed to write a formula on my sheet that counts the number of dates in a column. I have also counted the number of TBC values in another. How do I go about counting the number of Date values in first column that contain TBC in the other column? In hope of explaining this better the dates are for entries that have a scheduled date, but I want to find the amount of those that are scheduled, but don't have something confirmed in place for (TBC in the other column). For instance the below would result in 1

Actual Date	Schedule Date
10-Mar-13	24-Mar-13
18-Mar-13	24-Mar-13
18-Mar-13	24-Mar-13
TBC	24-Mar-13
18-Mar-13	24-Mar-13

<colgroup><col width="187" style="width: 140pt; mso-width-source: userset; mso-width-alt: 6838;"> <col width="183" style="width: 137pt; mso-width-source: userset; mso-width-alt: 6692;"> <tbody>
</tbody>

 

[Andrew Poulsom]

Welcome to MrExcel.

One way:

=SUMPRODUCT(--(A2:A6="TBC"),--(ISNUMBER(B2:B6)))

 

[Aladin Akyurek]

Hi, I'm new to formulas and new to the site. I have been using some video training in hope of getting the info I require. But at the moment I'm struggling to find any videos that actually are in line with what I am trying to do. Please bare with me as I'm a beginner. I have managed to write a formula on my sheet that counts the number of dates in a column. I have also counted the number of TBC values in another. How do I go about counting the number of Date values in first column that contain TBC in the other column? In hope of explaining this better the dates are for entries that have a scheduled date, but I want to find the amount of those that are scheduled, but don't have something confirmed in place for (TBC in the other column). For instance the below would result in 1

Actual Date	Schedule Date
10-Mar-13	24-Mar-13
18-Mar-13	24-Mar-13
18-Mar-13	24-Mar-13
TBC	24-Mar-13
18-Mar-13	24-Mar-13

<tbody>
</tbody>

Try one of:

=COUNTIFS(B2:B6,"<>",A2:A6,"TBC")

=SUMPRODUCT(--ISNUMBER(B2:B6),--(A2:A6="TBC")

Or control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(B2:B6),(A2:A6="TBC")+0))