I need a formula that will tell me if a customer has participated in an event at all during the year. The data is divided into 4 quarters (each quarter in it's own spreadsheet). There is a "Y" indicating if the customer participated. I need to know if they participated in any across the 4 sheets (if yes, "Y", if no, blank). I need this formula to fill down a column and correspond with the customer ID. Also, it needs to be like a vlookup since the customer IDs across the 4 sheets may not be in the same cells in the column. Although all the spreadsheets have the same headers.

Column A is the Customer ID and Column K is participation. This is true for all 4 Qtrs and the final year-end sheet where I want to put my formula.

message edited by Lisa99

Here is a SUMPRODUCT() & COUNTIFS() solution. First you need to define a Named Range for your

individual woeksheets, so someplace out of the way, on your Summary Sheet,

like column AZ for example, make a list of your worksheets:AZ 1) Sheet1 2) Sheet2 3) Sheet3 4) Sheet4Next, on the ribbon

select the Formula Tab,

select Define Name,

A popup window should appear

In the Name Box enter a range name, I have used: My_Sheet_Names

In the Scope box, it should read Workbook, so just leave it as that.

In the Comment box you can write anything you like to remined you

what this named range is for.

In the Refers To box enter: =Summary!$AZ$1:$AZ$4

Click OKNow, with your ID's in column A,

A 1) User ID 2) 123 3) 124 4) 125 5) 126 6) 127 7) 128 8) 129 9) 130In cell B2 enter the formula:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&My_Sheet_Names&"'!$A:$A"),A2,INDIRECT("'"&My_Sheet_Names&"'!$K:$K"),"y"))

Now simply drag the formula down as many rows as needed.

The formula makes the assumption that column A, in all the worksheets, is the ID column and that column K, in all the worksheets, is the Y/N column.

See how that works for you

And in the future, Excel questions are best asked in the Office Software forum.

MIKE

message edited by mmcconaghy

Thank You!!! It worked. It's a little slow, but I don't need it to be lightening fast since it's only a one time of year calculation. Really appreciate your help!

It's a little slow,How many ID's are you going thru?

Two suggestions that might speed things up a bit.

In the formula I used the entire column, IE $A:$A and $K$K

Try changing those to the actual range of your data,

so if you have 500 rows of data with one header row,

the formula would be something like:=SUMPRODUCT(COUNTIFS(INDIRECT("'"&My_Sheet_Names&"'!

$A2:$A500"),A2,INDIRECT("'"&My_Sheet_Names&"'!$K2:$K500"),"y"))Also, Sort your data, if it isn't already.

See if that helps.

MIKE

Or, if the range might change size, create a Dynamic Named Range so that the formula will always refer to exact size of the list.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll

Do you think facial recognition technology should be regulated?

Discuss in The Lounge

Poll History