generaleddie

New Member
Joined
Feb 12, 2019
Messages
2
Hi,
I'm getting the error too few arguments for this function with the below formula, any tips on where I am going wrong? I have simplified my table below but, basically, have multiple trusts donating to different charities and want to know the distinct count of beneficiaries for each charity, named in the table as One, Two and Three. In table below, for example, the results would be: One - 4, Two - 2, Three - 1.

=SUMPRODUCT(('Data £'!F:F="Three")/(COUNTIFS('Data £'!C:C, 'Data £'!C:C, 'Data £'!F:F="Three")+('Data £'!F:F<>"Three")))

CF
OrganisationCharities
Action Duchenne LimitedOne
Army Family FederationThree
Build Change One
Bursary StudentsTwo
Bursary StudentsTwo
Bursary StudentsTwo
Bursary StudentsTwo
Bursary StudentsTwo
Bursary StudentsTwo
Bursary StudentsTwo
Bursary StudentsTwo
Bursary StudentsTwo
Bursary StudentsTwo
Bursary StudentsTwo
Bursary StudentsTwo
Cambridge Heath Sixth Forms Two
Chailey Heritage FoundationOne
Children with Cancer UKOne

<tbody>
</tbody>

TIA!
 

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142
Hi, your COUNTIFS formula is off, you can't have only 3 arguments, you need to have either 2 or 4 (or any even number for that matter).
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

I think part of the problem - maybe not the only problem - is with your COUNTIFS function.

This slight variation at least doesn't return the "too few arguments" message . . .

=SUMPRODUCT(('Data £'!F:F="Three")/(COUNTIFS('Data £'!C:C, 'Data £'!C:C, 'Data £'!F:F,"Three")+('Data £'!F:F<>"Three")))

but I really don't know if it's doing what you want.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,816
is that what you want?

CharitiesDistinct Count of Organisation
One
4​
Three
1​
Two
2​
Grand Total
7
 

generaleddie

New Member
Joined
Feb 12, 2019
Messages
2
is that what you want?

CharitiesDistinct Count of Organisation
One
4​
Three
1​
Two
2​
Grand Total
7

<tbody>
</tbody>
Yes, that is the point I want to get to.
Why does the COUNTIFS formula have to have an even number of arguments?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
Why does the COUNTIFS formula have to have an even number of arguments?
Because that is how it works!

You have "sets" of conditions:
1. criteria range
2. criteria

So if you have 1 "set", that is 2 arguments. If you have 2 "sets", that is 4 arguments, etc.
You cannot have a criteria range without corresponding criteria, or criteria with its corresponding criteria range.
So it is impossible to have an odd number of arguments. If you try, you will get errors.

You can read up on COUNTIFS here: https://www.techonthenet.com/excel/formulas/countifs.php
 

sunilbsrv2k

Board Regular
Joined
May 25, 2018
Messages
60
Yes, that is the point I want to get to.
Why does the COUNTIFS formula have to have an even number of arguments?
I guess that's because, the syntax says Countifs(crieria1_Range1, criteria1....). Which means, for each range specified, there should be a criteria added.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
You can also get help on any Excel function by starting to type in the function name (e.g. =COUNTIFS( ) and then pressing either f1 for help, or the fx button to the left of the formula bar at the top of the screen.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,816
Yes, that is the point I want to get to.
use PivotTable
add to DataModel to get DistinctCount
then add Charities to Rows Area and Organistation to Values Area
from Organisation select Value Field Stttings and choose Distinct Count
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,816
another way is PowerQuery

CharitiesOrganistation
One
4​
Three
1​
Two
2​

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Charities"}, {{"Organistation", each Table.RowCount(Table.Distinct(_)), type number}})
in
    #"Grouped Rows"[/SIZE]
 

Forum statistics

Threads
1,082,246
Messages
5,363,995
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top