Sumproduct issue

[jackjackjack]

Hi All,

I have rows of data - each one representing an hour - obviously 24 to a day.
Each row has "Date", "Time" and "Flag".
The contents of Flag are either "Bad" or "Good".

I want each row to contain a cell that contains a count of the number of Bads for that day.

I know i can type countif() in every row - but i am looking at 20 years data!

Any ideas for a formula that I can copy and it will handle the job?

eg. using 4 hours to a day:


Date Time Flag Count
1/1 1:00 Bad 1
1/1 2:00 Good 1
1/1 3:00 Good 1
1/1 4:00 Good 1
2/1 1:00 Bad 3
2/1 2:00 Good 3
2/1 3:00 Bad 3
2/1 4:00 Bad 3



Any help appreciated

Jack

[Paul]

Hi Jack,

Try this formula in D2 and filled downward:

Please Login or Register  to view this content.

Adjust your range to suit, I used A2:A10000 as an example. Both ranges need to be the same size, though. (A2:A10 = C2:C10, for example)

[BigBas]

You can use a SUMPRODUCT formula.

Assume your data is in cells A1:C1000, enter this formula into cell D1:D1000

Please Login or Register  to view this content.

Report back if you need further assistance.

[jackjackjack]

Thanks for both your help.

I was really struggling with that - vlookup() - sumif() - row() etc etc.

But I plugged the sumproduct formula in and it works PERFECTLY.