How to count only cells with names (and not numbers), and other questions.

Hello,

I'm a very big noob at excel and am trying to learn the basics. I'm trying to use advanced filters to count each occurrence of a given name on a new sheet.

I'm encountering 2 problems:

The first problem I have is that sometimes there are dates in the same column as the names, and I don't want to count these dates. There are also blank cells which I do not want to count.
I'm inspired by the following tutorial:
https://www.youtube.com/watch?v=bGUKjXmEi2E
I'd like to filter out the numbers and blank cells so that only the names appear on my new worksheet.
What would be a good formula to only count the names and not the numbers and blank cells?

The second problem I'm encountering is that I only want the names to be counted when a value in the cell to the right is 1 or more. If the cell to the right of the name has a value of 0, the name shouldn't be counted. Again, what would be the best formula for this?

I hope this is clear enough

Thanks in advance for your help.

.......................................................

Not real clear what you want to do.

You mention the count of names but then you also ask that these names appear on another sheet.

How about posting a SMALL sample file and showing us what result you expect?

A SMALL file will have about 20 rows worth of data. We don't need files with 100's or 1000's of rows and dozens of columns worth of data. This just makes it harder to figure out what you want.

Ok here you go.
I apparently still have some ways to go to master the jargon.
I'll try to describe as best I can the effect I'm looking for.

Copy of sample file2.xls

I need to count all the names on sheet 1. The total must appear on sheet 2. The dates, as well as the blank spaces are not to be counted and should not figure on sheet 2.

However, the names only are to be counted on sheet 2 when Condition 1 is met.

Also, on sheet 2 the different variables under "data 2" must be included and added accordingly.

That's about it. Please tell me if you need more clarifications. I'm very new to this.

Thanks

How about updating the sample with the results you expect?

On a side note... Why is everything so big? Makes it harder to view the data structure.

Here's an updated sample file with the results on the second sheet. Please note that some of the data has been modified since the last uploaded file.

Is everything so big? I can't tell, that's just how it appears on my computer. Feel free to modify font size and any other parameters.

Copy of sample file2.xls

Thanks again and please don't hesitate to ask for details if anything appears unclear.

I think this is what you want...

Data Range

	B	C	D	E	F	G	H
1		Data 2
2	Name	Variable 9	Variable 10	Variable 11	Variable 12	Variable 13	Variable 14
3	Milly	0	2	1	0	0	0
4	Louisa	0	0	0	1	1	0
5	Fred	0	0	0	0	0	1
6	Sylvester	1	0	0	0	0	1
7	Carl	0	0	0	1	0	0
8
9	------	------	------	------	------	------	------

Enter this array formula** in B3:

=IF(ISERROR(INDEX(Sheet1!C$6:C$16,MATCH(0,IF(Sheet1!C$6:C$16<>"",IF(Sheet1!O$6:O$16=1,COUNTIF(B$2:B2,Sheet1!C$6:C$16))),0))),"",INDEX(Sheet1!C$6:C$16,MATCH(0,IF(Sheet1!C$6:C$16<>"",IF(Sheet1!O$6:O$16=1,COUNTIF(B$2:B2,Sheet1!C$6:C$16))),0)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Enter this formula in C3:

=IF(B3<>"",SUMIF(Sheet1!$C$6:$C$16,$B3,INDEX(Sheet1!$P$6:$U$16,0,MATCH(C$2,Sheet1!$P$5:$U$5,0))),"")

Copy across to H3 then down as needed.

If you don't want to see the 0 values we can use conditional formatting to hide them.

Whoa! That looks like two very complex formulas.
Thanks so much.
However, I must be doing something wrong, because I keep getting "The formula contains an error" message when I try to copy+paste any of these formulas into sheet2 or sheet2 (2).

In the case of the array formula, when pressing CTRL+SHIFT+ENTER, sometimes the formula simply appears as is within the cell (in B3), and sometimes the same error message appears. Also, "C$16,MATCH" is highlighted in the formula bar.

As for the second formula (the one in C3), it doesn't appear to work either as I get the same message. What am I doing wrong?

BTW I'm using the sample file included above to test the formulas.

Tony's 1st formula is the basis for the others. It is an ARRAY formula, and needs to be entered using CTRL SHIFT ENTER, not just enter.
After entering the 1st 1 like that, you can copy it down further. the otrher formula is a regular formula, entered the normal way

Originally Posted by FDibbins

Tony's 1st formula is the basis for the others. It is an ARRAY formula, and needs to be entered using CTRL SHIFT ENTER, not just enter.
After entering the 1st 1 like that, you can copy it down further. the otrher formula is a regular formula, entered the normal way

So far, this is what I do:

1. highlight the B3 cell.
2. click on the formula bar
3. copy and paste the array formula into the formula bar
4. press CTRL SHIFT and ENTER

I've also tried this:

1. highlight the B3 cell.
2. copy and paste the array formula directly into the cell
3. press CTRL SHIFT and ENTER

Despite these attempts, the error persists. I'm probably doing something wrong, but I can't tell what.

If you already have the formula in the cell, press F2 (edit), then press CTRL SHIFT ENTER. Dont just press CSE while just "on" the cell, you need to edit the cell.
If you do not have anything in B3, then copy/paste the formula to the formula bar, THEN press CSE

You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Attached is your sample, with Tony's formula

Originally Posted by FDibbins

You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Ok, thanks for the file.

So now I tried this:

1. highlight the B3 cell.
2. click on the formula bar
3. press F2
4. copy and paste the array formula into the formula bar
5. press CTRL SHIFT and ENTER

And this:

1. highlight the B3 cell.
2. press F2
3. copy and paste the array formula directly into the cell
4. press CTRL SHIFT and ENTER

I see the curly braces { } in your attached file, but when I press F2 on my initial sheet, nothing appears to happen (that is, the curly braces { } do not appear in the cell nor in the formula bar).

Also, I've tried pressing F2 before and after I copy and paste the formula, but still nothing happens.

I'm following your indications as close as I can. You all explain very clearly, and I don't understand why it doesn't work... Those { } curly braces just don't want to appear...

I've even tried using both left and right Ctrl+Shift keys. Is there any way around this? Excel 2003 does use array formulas, right?

Yes, excel does use ARRAY's

Dont press F2 after you enter using CSE.

Lets assume you already have a copy of the formula in the cell...

1. make sure teh cursor is on the cell (click ONCE on the cell, if it not)
2. Press F2
3. press and hold CTRL (dont let go yet)
4. Press and hold SHIFT (dont let go yet)
5. Press ENTER (you can now let go of the other 2 keys)
You should now see the {}

That is all you need to do - do NOT press F2 or edit the cell after (5). If you do need to, then follow steps 1-5 again

Originally Posted by FDibbins

Lets assume you already have a copy of the formula in the cell...

1. make sure teh cursor is on the cell (click ONCE on the cell, if it not)
2. Press F2
3. press and hold CTRL (dont let go yet)
4. Press and hold SHIFT (dont let go yet)
5. Press ENTER (you can now let go of the other 2 keys)
You should now see the {}

This is what I've been doing, but the {} don't appear and I get the aforementioned error message as soon as I press enter.

I've searched google for problems concerning CSE, but still no luck.

Can you upload what you have?

Of course.

Sample file 3 contains as much as I'm able to do despite errors. It's essentially identical to the last sample file I've uploaded (sample file 2).

Copy of sample file3.xls

Copy of sample file2.xls

You'll notice the absence of { } in both files...

I opened file3.xls, pressed F2 held down CTRL and SHIFT and pressed ENTER, and it worked

In C3, not sure why is is showing like that, I pressed F2, then enter

I was able to CTRL+SHIFT+ENTER using the example on the following page:

https://support.office.com/en-au/art...6e9ecee523#bm1

The array formula worked! And I got the { }! So now I know that it is indeed possible!

However, when I try to copy and paste Tony Valco's code, I keep getting the error message.
Here's his code once again:

=IF(ISERROR(INDEX(Sheet1!C$6:C$16,MATCH(0,IF(Sheet1!C$6:C$16<>"",IF(Sheet1!O$6:O$16=1,COUNTIF(B$2:B2,Sheet1!C$6:C$16))),0))),"",INDEX(Sheet1!C$6:C$16,MATCH(0,IF(Sheet1!C$6:C$16<>"",IF(Sheet1!O$6:O$16=1,COUNTIF(B$2:B2,Sheet1!C$6:C$16))),0)))

The frustrating thing about this error message is that I still have no indication as to what I'm doing wrong. Can anyone help?

edit: I just found this site with translations of english formulas into french ones. I had no clue Excel functions are language specific! Could this explain the error messages? I'll be trying it out tonight, fingers crossed.

Sadly, entering the formulas in another language does not work either.

Sorry for insisting, but its very frustrating to see that despite following your simple instructions, the formula doesn't appear to work. Can any of you people please help me find a way around this?

I trust you believe me when I say I get an error despite effectively pressing CTRL+SHIFT+ENTER (the short-cut isn't exactly complicated).

Is there any way around short-cut keys?

If not, is there any way to troubleshoot array formulas to see when things go wrong?

Perhaps it's a problem with the formula? Could it be that it isn't compatible on my version of excel?

The segment "C$16,MATCH" of the formula is highlighted after the error appears.

In some international locations they use the semi-colon as the default delimiter. In the U.S. we use the comma. For example...

U.S. =SUM(A1,D2)

Some other location would use =SUM(A1;D2)

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

I was just about to mark the question as solved when I encountered another problem. Hopefully it's going to be the final one (and easy one).

I'm now able to use array formulas in the sample document I uploaded.

However, my initial document is huge, and has about 10 sheets full of names and data.

When I copy and paste the array formula into my initial document, I don't get an error message. But, instead, I only get a blank cell...

The cell layout is identical to the one in the sample document. I actually used my initial document as a template for the sample I've uploaded.

Why would I get blank cells when copying the formula into my initial document, if I was able to make it work in the sample document?

The array formula should go into B3...
The other formula should go into C3...

Thanks for helping me figure out this final bit, and sorry for the hassle!

Would have to see the file.

If it's a huge file then I have to pass as I have file size limits!

I've once again taken my initial document, and modified it. For simplicity sake, there's only one data sheet. The elements of 'Janvier 2015' must be copied into 'RESULTATS'. I've taken the care of removing all names for confidentiality.

I've tried applying the formula above, now that I kind of understand how array formulas work. However when I do so I only get blank cells. As far as I can see, the setup is identical to the sample document, only there are many more rows.

I'm assuming once this gets working I'll be able to fill the blanks and simply insert the extra pages... right?

Attachment 404312

The file is too big for me.

Ok nevermind, I'll figure it out. I think if I extend the range of the columns, it should work fine. I'll mark this topic as solved...

I'm really sorry about this, but I've still got a few questions. The amount of time this is taking is in part my own fault. Many thanks for your patience.

Now that the array formula question is solved, I'm happy to say that this first element of my sheet works flawlessly. I am amazed at excel's power. The second one, on the other hand, perhaps still needs fine tuning. Once again, my fault: I was not clear enough the first time around about the results I need. I have two small questions, all regarding the second formula:

=IF(B3<>"",SUMIF(Sheet1!$C$6:$C$16,$B3,INDEX(Sheet1!$P$6:$U$16,0,MATCH(C$2,Sheet1!$P$5:$U$5,0))),"")

For easy reference, here's a link to the post where this formula is first proposed.

1. The data from a given row in columns P to U (variables 9 to 14) is to be counted only if there is a 1 in the corresponding row of column O (condition 1). I've added yet another sample file to illustrate what I mean. You'll now see a slight difference between your data and the results the results I expect. I hope it's clear enough. Once again: the 'variables' should only be counted when 'condition 1' is met.

Copy of sample file 2nd formula.xls

2. Despite the formula working flawlessly in the sample file, I sometimes get #N/A in the cells when I try to copy the cell. This happens exclusively when I try to copy the cell to the right. This is no doubt another noob mistake, but I've been playing around with the $ signs since last night without any luck. What could be the cause of this? What could I be missing?

Any help is once again greatly appreciated.

For question 1...

This formula entered in C3 on Sheet2:

=SUMPRODUCT(--(Sheet1!$C$6:$C$18=$B3),--(Sheet1!$O$6:$O$18=1),INDEX(Sheet1!$P$6:$U$18,0,MATCH(C$2,Sheet1!$P$5:$U$5,0)))

Copy across to H3 then down as needed.

Question 2...

Thanks once again for the formula.

I've solved the second question. Once again, the error was simply because of sloppy planning.

I have just one last question please (I really mean it).

What would be the best way to add up (in a new column) each time a given name (ex:B3-B7) coincides with 'condition 1' of sheet1 (O3-07)?

For example, on sheet1, Milly and 'Condition 1' coincide twice. Louisa, Fred, Sylvester and Carl each have one single 'Condition 1' marked. All other names from sheet1 do not figure on this list...

Here are the expected results (some data from sheet1 of the sample document is required):

	Variable 9	Variable 10	Variable 11	Variable 12	Variable 13	Variable 14	TOTAL
Milly		2	1				2
Louisa				1	1		1
Fred						1	1
Sylvester	1					1	1
Carl				1			1

If I understand, I think this will do that:

=SUMPRODUCT(--(Sheet1!$C$6:$C$18=$B3),--(Sheet1!$O$6:$O$18=1))

You're welcome. Thanks for the feedback!

I'm very sorry about this, but I'm still encountering a few problems, despite all of the time invested in my project.

Should I keep posting here, or start a new topic?

Here's my question, which always concerns the document we've been talking about in this thread up to now:

I've got several sheets with names, which all have a layout identical to sheet1.

I thought that by replacing sheet1 with the name of the other sheets (ex: sheet14), I would be able to handle the data the same way. Alas, when I try to replace sheet1 by sheet14 on my 'results' sheet, I only get a blank cell instead of names. This happens even though the layout of sheet14 is identical to sheet1. Is there anything I'm forgeting? What could I be doing wrong here?

Post an updated sample file with a couple of sheets (we don't need a file with 10's of sheets!).

Remember, it's a SAMPLE file and should only include enough data to demonstrate your problem. All other unrelated/irrelevant data just makes it harder to analyze your data and solve the problem.

The resulting formulas will be extremely complex. Orders of magnitude more complex than the formulas that have already been suggested in this thread.

I would suggest a summary table on each sheet in the exact same format in the exact same location then you could get a grand summary from those tables much more easily.

I think I understand. But this might be difficult because I'm not the author of the initial sheets and am not necessarily authorised to modify them (or more precisely, I'd rather be able to simply build on the existing sheets without too many excess sheets). Also, please consider that I eventually did succeed in using the formulas. If such formulas are possible, I am very motivated in attempting to use them.

If they really are too complicated, I'll of course try the summary table suggestion, even if it means using extra sheets to combine the data.

Ok, I'll update the sample file.

Does this have to work in Excel 2003 or can it be designed to work in later versions?

You'll have to enter the sheet names in a HORIZONTAL range of cells. In the sample file I've used the range K3:L3 and given those cells the defined named range of Sheets.

If your real sheet names follow some sequential naming pattern then we can place the sheet names directly into the formula (which will make the formulas even more complex and long!).

Each sheet included in the summary MUST have the EXACT same layout.

Enter these formulas on Sheet2 in the respective cells:

C3:

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&Sheets&"'!C6:C16"),ROW(INDIRECT("C6:C16"))-6,,))=B3),--(N(OFFSET(INDIRECT("'"&Sheets&"'!O6:O16"),ROW(INDIRECT("O6:O16"))-6,,))=1),--(N(OFFSET(INDIRECT("'"&Sheets&"'!P6:P16"),ROW(INDIRECT("P6:P16"))-6,,))=1))

D3:

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&Sheets&"'!C6:C16"),ROW(INDIRECT("C6:C16"))-6,,))=B3),--(N(OFFSET(INDIRECT("'"&Sheets&"'!O6:O16"),ROW(INDIRECT("O6:O16"))-6,,))=1),--(N(OFFSET(INDIRECT("'"&Sheets&"'!Q6:Q16"),ROW(INDIRECT("Q6:Q16"))-6,,))=1))

E3:

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&Sheets&"'!C6:C16"),ROW(INDIRECT("C6:C16"))-6,,))=B3),--(N(OFFSET(INDIRECT("'"&Sheets&"'!O6:O16"),ROW(INDIRECT("O6:O16"))-6,,))=1),--(N(OFFSET(INDIRECT("'"&Sheets&"'!R6:R16"),ROW(INDIRECT("R6:R16"))-6,,))=1))

F3:

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&Sheets&"'!C6:C16"),ROW(INDIRECT("C6:C16"))-6,,))=B3),--(N(OFFSET(INDIRECT("'"&Sheets&"'!O6:O16"),ROW(INDIRECT("O6:O16"))-6,,))=1),--(N(OFFSET(INDIRECT("'"&Sheets&"'!S6:S16"),ROW(INDIRECT("S6:S16"))-6,,))=1))

G3:

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&Sheets&"'!C6:C16"),ROW(INDIRECT("C6:C16"))-6,,))=B3),--(N(OFFSET(INDIRECT("'"&Sheets&"'!O6:O16"),ROW(INDIRECT("O6:O16"))-6,,))=1),--(N(OFFSET(INDIRECT("'"&Sheets&"'!T6:T16"),ROW(INDIRECT("T6:T16"))-6,,))=1))

H3:

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&Sheets&"'!C6:C16"),ROW(INDIRECT("C6:C16"))-6,,))=B3),--(N(OFFSET(INDIRECT("'"&Sheets&"'!O6:O16"),ROW(INDIRECT("O6:O16"))-6,,))=1),--(N(OFFSET(INDIRECT("'"&Sheets&"'!U6:U16"),ROW(INDIRECT("U6:U16"))-6,,))=1))

Select the range C3:H3 and copy down as needed.

I have disabled the display of 0 values (Tools>Options>View>Zero values)

Here's your file with these formulas implemented.

Ok, I'm looking into it now.

The sheet names are the months:

January, February, April, March...

Each sheet has the same layout, but some have more entries than others.

The formula is good, but I have just one more request. Would it be possible to formulas for the names too? That is, for column B? Each name should appear only once.

Regarding the layout, I'd just like to add that every sheet does not necessarily have the same names, in the same order.

Originally Posted by SperryACHI

Would it be possible to formulas for the names too? That is, for column B? Each name should appear only once.

I don't know if that's possible with formulas.

Let me see if I can come up something but don't hold your breath!