Lookup and Countif combination

[blackhawk]

Hi. I'm trying to use a combination of Hlookup and COUNTIF. I'm selecting a date value in a cell using data validation. I'm then wanting to write a formula to lookup that value in a row of dates, and then use a countif to find all the '1' values in that column.
Could anyone help?

[NBVC]

Something like this:

=SUMPRODUCT((E2:H2=A1)*(E3:H8=1))

where E2:H2 contains row of dates... and A1 contains choice date...

E3:H8 is table underneath dates row containing 1's, etc...

adjust ranges to suit.

[blackhawk]

Something like this:

=SUMPRODUCT((E2:H2=A1)*(E3:H8=1))

where E2:H2 contains row of dates... and A1 contains choice date...

E3:H8 is table underneath dates row containing 1's, etc...

adjust ranges to suit.

That works fine. Spent hours trying to get my head around this!!
Thanks loads.

[Special-K]

Use SUMPRODUCT with unary "--" to count, e.g.

=SUMPRODUCT((A1:A10=date)*--(B1:B10=1))

[NBVC]

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[costadina]

Here's another way

=COUNTIF(OFFSET(F2:F20,0,MATCH(A1,F1:O1)-1),1)

where:
A1 holds the date entry
F2:F20 is the first column of data values from row 2 to 20 (0,1,2,3,4,etc..)
F1:O1 is the table header, which holds the different dates, one for each column from F to O. (May 1, May 2, etc...)

That was fun

[NBVC]

though OFFSET is volatile.