I can't do this for the life of me. I need to find a way to count how many times a particular Date appears in Column A if and only if Column B has a 1 in that row. I need something that is easily autofilled as well since I am trying to make this sheet as easy to use and expand as possible.
2007-09-25 15:58:04 · 4 answers · asked by Anonymous in Computers & Internet ➔ Software
If your data is in rows 1 to 100, and the date you're looking for is in D1:
=SUMPRODUCT((A1:A100=D1) * (B1:B100=1))
If you need more info, add more detail, or e-mail.
Cheers.
.
2007-09-25 16:29:01 · answer #1 · answered by aladou 5 · 3⤊ 1⤋
If what you mean is to count the number of times a particular date appears in column A provided column B shows no 1.
Say you have the number 1 in cell B3 and the date for which we want to count the number of times is in A3
Type in C3 the following formula
=IF(B3=1,COUNTIF ($A$1:$A$10,B3),"")
$A$1:$A$10 = Column A date range
Then copy it down
2007-09-26 15:14:25 · answer #2 · answered by voyager 6 · 0⤊ 1⤋
Add a hidden column that concatenates Column A and Column B, then use =CountIf to test the new hidden column.
2007-09-25 23:29:52 · answer #3 · answered by Daniel T 5 · 0⤊ 3⤋
I agree with aladou. Just change the ranges accordingly.
2007-09-26 12:08:45 · answer #4 · answered by devilishblueyes 7 · 0⤊ 2⤋