| View previous topic :: View next topic |
| Author |
Message |
Marcos.123
General User
Joined: 01 Mar 2011
Posts: 9
|
 Posted: Tue Mar 15, 2011 12:15 pm Post subject: [SOLVED] Count.ifs |
 |
|
How can I count with more than one criteria? Does Calc have cont.ifs function?
Last edited by Marcos.123 on Wed Mar 16, 2011 9:39 am; edited 3 times in total |
|
| Back to top |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1847
Location: Sydney, Australia
|
| Posted: Tue Mar 15, 2011 1:56 pm Post subject: |
|
|
Use the SUMPRODUCT function.
For example, to count the rows where A1:A50=D1 and B1:B50=D2...
| Code: | | =SUMPRODUCT(A1:A50=D1;B1:B50=D2) |
or | Code: | | =SUMPRODUCT((A1:A50=D1)*(B1:B50=D2)) |
To count the rows where A1:A50=D1 or B1:B50=D2... | Code: | | =SUMPRODUCT((A1:A50=D1)+(B1:B50=D2)-(A1:A50=D1)*(B1:B50=D2)) |
Edit: Corrected formula for counting the rows where A1:A50=D1 or B1:B50=D2
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Last edited by ken johnson on Sat Mar 19, 2011 5:16 pm; edited 1 time in total |
|
| Back to top |
|
|
Marcos.123
General User
Joined: 01 Mar 2011
Posts: 9
|
| Posted: Wed Mar 16, 2011 8:27 am Post subject: |
|
|
Sorry, it didn't work. My problem is that the criteria for counting are texts. Like this:
=SUMPRODUCT('Tabela de Pendências'.C4;C65536="Vistoria Prévia"; 'Tabela de Pendências'.D4:D65536="ABC")
And then, Calc. gives the error #NAME?
Thanks for your help!
Last edited by Marcos.123 on Wed Mar 16, 2011 9:27 am; edited 1 time in total |
|
| Back to top |
|
|
Marcos.123
General User
Joined: 01 Mar 2011
Posts: 9
|
| Posted: Wed Mar 16, 2011 9:39 am Post subject: |
|
|
| Thank you! |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8979
Location: Lexinton, Kentucky, USA
|
| Posted: Wed Mar 16, 2011 9:45 am Post subject: |
|
|
| You used a semicolon instead of a colon. |
|
| Back to top |
|
|
Marcos.123
General User
Joined: 01 Mar 2011
Posts: 9
|
| Posted: Thu Mar 17, 2011 10:29 am Post subject: |
|
|
| yeah. I saw that when i wrote this hier. |
|
| Back to top |
|
|
|
