I have a Numbers spreadsheet divided into five sheets. I wish to insert a text word “BUY” into cell A1 of sheet 5 providing any two of cells A1 in Sheets 1, 2, 3, and 4 contain the word “BUY”. Many thanks to anyone who can solve this.
iMac, Mac OS X (10.7.3), 2.7 GHz Intel Core i5 Memory 4 GB
You can set up like this:
Sheet 1 :: Table 1 :: A1 = "BUY"
Sheet 2 :: Table 1 :: A1 = "BUY"
Sheet 3 :: Table 1 :: A1 = ""
Sheet 4 :: Table 1 :: A1 = ""
Sheet 5 :: Table 1 :: A1 =IF((COUNTIF(Sheet 1::Table 1::A1, "BUY")+COUNTIF(Sheet 2::Table 1::A1, "BUY")+COUNTIF(Sheet 3::Table 1::A1, "BUY")+COUNTIF(Sheet 4::Table 1::A1, "BUY"))≥2, "BUY", "")
This is shorthand for select cell A1 of Table 1 in Sheet 5 the type (or copy and paste from here) the formula:
=IF((COUNTIF(Sheet 1::Table 1::A1, "BUY")+COUNTIF(Sheet 2::Table 1::A1, "BUY")+COUNTIF(Sheet 3::Table 1::A1, "BUY")+COUNTIF(Sheet 4::Table 1::A1, "BUY"))≥2, "BUY", "")
You can set up like this:
Sheet 1 :: Table 1 :: A1 = "BUY"
Sheet 2 :: Table 1 :: A1 = "BUY"
Sheet 3 :: Table 1 :: A1 = ""
Sheet 4 :: Table 1 :: A1 = ""
Sheet 5 :: Table 1 :: A1 =IF((COUNTIF(Sheet 1::Table 1::A1, "BUY")+COUNTIF(Sheet 2::Table 1::A1, "BUY")+COUNTIF(Sheet 3::Table 1::A1, "BUY")+COUNTIF(Sheet 4::Table 1::A1, "BUY"))≥2, "BUY", "")
This is shorthand for select cell A1 of Table 1 in Sheet 5 the type (or copy and paste from here) the formula:
=IF((COUNTIF(Sheet 1::Table 1::A1, "BUY")+COUNTIF(Sheet 2::Table 1::A1, "BUY")+COUNTIF(Sheet 3::Table 1::A1, "BUY")+COUNTIF(Sheet 4::Table 1::A1, "BUY"))≥2, "BUY", "")
Dear Wayne Contello,
Many thanks for your help - the formula you gave worked with no trouble at all and I will be able to continue with the project I am involved with. I kept getting syntax errors with what I was trying before and your advice has been "just what the doctor ordered"!
Kind regards,
Weaver 158
Dear Wayne Contello,
Further to my original post I find that if I use the formula specified below I get #REF! errors for Sheets 3, 4 for some unknown reason the Sheet 5::Table 1 disappeared from Sheet 5 and I was left with just H1074. The following formula was copied and pasted into Sheet 5 Cell J1074:-
=IF((COUNTIF(Sheet 1::Table 1::E3073, "BUY")+COUNTIF(Sheet 2::Table 1::D3073, "BUY")+COUNTIF(Sheet 3::Table 1::F1074, "BUY")+COUNTIF(Sheet 4::Table 1::F1074, "BUY”)+COUNTIF(Sheet 5::Table 1::H1074,”BUY”))≥2, "BUY", "")
The errors are shown as:-
=IF((COUNTIF(Sheet 1::Table 1::E3073, "BUY")+COUNTIF(Sheet 2::Table 1::D3073, "BUY")+COUNTIF(#REF!, "BUY")+COUNTIF(#REF!, "BUY")+COUNTIF(H1074,"BUY"))≥2, "BUY", "")
Am I right in assuming that because Cells F1074, (Sheet 3), F1074 (Sheet 4), and H1074 (Sheet 5) are different Rows to Sheets 1 and 2 which used E3073 and D3073 they are unacceptable to the COUNTIF command?
Is there any way that this problem can be solved or is it necessary to change the overall dimensions of Sheets 3, 4 and 5 so that the correspond with Sheets 1 and 2?
Any suggestions you can make will be very welcome.
Kind regards,
Weaver 158
I suggest checking that each of the sheets and tables you reference exist.
Does "Sheet 3" exist?
Does "Sheet 3" contain a table named "Table 1"?
Same questions for "Sheet 4" and "Table 1"
Dear Wayne Contello,
Thanks for your quick reply. I have looked at the spreadsheet containing the 5 different sheets and they all appear to be OK. The Table 1 Header lists each of the individual Sheets under their respective numbers along the header bar (Sheet 1, Sheet 2, Sheet 3, Sheet 4 and Sheet 5). Each of these Sheets is large and covers a lot of price information and I can access each one individually by clicking the appropriate Sheet number in the header bar which is then accented in blue when the Sheet appears on screen.
 I have tried writing the formula covering Sheets 1 and 2 only and it works OK. However when it is extended to cover Sheets 3, 4 and 5 I get the results that I have outlined in my previous post.
Any further advice you may be able to offer will be welcomed.
Kind regards, Weaver 158
Hi Weaver,
If the Sheets::Tables::cells you are trying to reference exist then you can fix your formulas this way:
Click on #Ref!
navigate to the cell you need to reference, click on it
repeat till done, hit enter
quinn
Dear Quinn,
Thanks for your advice which I have followed. I am sorry to say that it has not produced the result I was hoping for. The resulting formula now reads as follows:-
=IF((COUNTIF(Sheet 1::Table 1::E3073, "BUY")+COUNTIF(Sheet 2::Table 1::D3073, "BUY")+COUNTIF(Sheet 3::'Momentum 95/105'::F1074,, "BUY")+COUNTIF(Momentum Variable::F1074, "BUY")+COUNTIF(H1074,"BUY"))≥2, "BUY", "")
The formula I was hoping would appear is:-
=IF((COUNTIF(Sheet 1::Table 1::E3073, "BUY")+COUNTIF(Sheet 2::Table 1::D3073, "BUY")+COUNTIF(Sheet 3::Table 1::F1074, "BUY")+COUNTIF(Sheet 4::Table 1::F1074, "BUY”)+COUNTIF(Sheet 5::Table 1::H1074,”BUY”))≥2, "BUY", "")
On accepting the incorrect formula the Cell H1074 on Sheet 5 has a red triangle in it which on clicking says “Countif requires 2 arguments but was given 3”.
Any further advice will be welcome.
Kind regards,
Weaver 158.
Hi Weaver,
The error message is saying that in one of your COUNTIF formulas you have at the least an extra comma. It seems to me (and I am not trying to understand your formulas, just proof read them) that there is an extra comma here:
COUNTIF(Sheet 3::'Momentum 95/105'::F1074,, "BUY")
The last part of your formula:
COUNTIF(H1074,"BUY"))≥2, "BUY", "") is different from
COUNTIF(Sheet 5::Table 1::H1074,”BUY”))≥2, "BUY", "") because you are entering the formula in Sheet 5::Table 1 and that info is unnessasary.
If the formula is referencing the wrong tables or the wrong cells I think you may have to take responcibility 😉 and go back and click where you actually want. Maybe the table is not the table you think it is. It looks like you are clicking in Tables " 'Momentum 95/105' " (whats with the quotes in the title?) and "Momentum Variable" instead of "Table 1" of those sheets.
"for some unknown reason the Sheet 5::Table 1 disappeared from Sheet 5 and I was left with just H1074" this does not make sense. You cannot have a cell without a table. You can have a single cell table. Perhaps you are running a filter or have hidden rows/columns.
It is recomended to stay away from complicated, long, table names, particularly names with many spaces and special characters (i.e. "/") this can invite problems.
quinn
Hi Weaver,
An extra comma has crept into the formula
Regards,
Ian.
Hi quinn, great minds... 😉. Ian.
Hi Yellowbox,,
Thanks for your timely advice. You will be pleased to hear that everything is now just fine and my project is going well.
Kind regards,
Weaver 158
Dear Quinn,
Your suggestion sorted things out and everything is now working fine. Thanks for your advice which I appreciate.
Kind regards,
Weaver 158
Numbers formula problem
