Another Countif or Sumproduct Question

[Notanexpert]

I have a multiple worksheet excel file. One page lists clients name and number, and around 15 columns to indicate the intervention they are being submitted for. Each entry is input as a date under the correct column (intervention) by client (row). On another summary page I have a mirror of this page, but want to display the number of each intervention for a client. This would also depend on the client entered.

I have fought with this for several days now and have come close but not quite. Having searched your very helpful pages, I thought I would post my question.

So I want to count the number of entries (counta) in the column, but only if the client number matches the row - ie cannot say countif(a:a = "Smith") but countif(a:a = "worksheetnameA7). I have made these formulae up - they are not ones I have used (LOL)!

[NBVC]

Something like:

=Countif(A:A,'worksheetname'!A7)

If you want to also check that a date is entered in a specific corresponding field...then something like:

=Sumproduct(--(A1:A1000='worksheetname'!A7),--(B1:B1000<>""))

where B1:B1000 contains the intervention dates, if any.

Note: With Sumproduct, unless you are using XL2007, you can't use full column references and so need to set a defined range.

[Notanexpert]

Thanks for the very speedy response - its much appreciated.

I should say that the information I need to count is on the other worksheet, both the intervention details and the clients name & number. The solution you gave (which I hurriedly tried with great hope - adding the <<worksheetname>> to the start of a:a - doesn't work.

The details worksheet has the client number in the C column - and the intervention info is in the D, F, H, etc column. The summary page is more or less the same, and I want to give staff the option of typing in any client number (I already have the spreadsheet entering the correct name) and then it lists how many interventions that client has had - by matching the number given (I assume a cell reference is important here). What I didn't make clear was that the same client could be listed further down the page and we'd need to count all interventions for that client number on the sheet.

HELP!

[NBVC]

So you need to count if a client name appears in the Details sheet and that there is a date in either column D, F or H in the same sheet corresponding to that client or that there is a date in D, F and H in the same sheet?

Maybe a sample workbook would be helpful to attach here.

[Notanexpert]

I'm hopefully posting an example

[NBVC]

Try:

=SUMPRODUCT(--('Client Activity'!$C$4:$C$300='Client Summary'!A7),--('Client Activity'!$D$4:$D$300<>""))

[Notanexpert]

THANK YOU! THANK YOU! THANK YOU!

My sanity has now returned.