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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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).
 
Upvote 0
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.
 
Upvote 0
is that what you want?

CharitiesDistinct Count of Organisation
One
4​
Three
1​
Two
2​
Grand Total
7
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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
Back
Top