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
4,159
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,731
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
67
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
4,159
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
4,159
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,084,878
Messages
5,380,415
Members
401,673
Latest member
Ali Balleya

Some videos you may like

This Week's Hot Topics

Top