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!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

ADVERTISEMENT

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
55,882
Office Version
  1. 365
Platform
  1. 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
71

ADVERTISEMENT

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top