Combine Formulas Help

[newcommer]

Hi there,

i have 2 formula's require combining but not's not happening for me

explanation - have a database for staff need to count all employees on list, not counting duplicates ( as some employees down twice due to working area)
I use the below formula - success

=SUMPRODUCT(1/COUNTIF('Employees '!A2:A1000, 'Employees '!A2:A1000))

I then have drop down lists 3, area, department, active - which then filter out the employees depending on what is selected

=COUNTIFS(Master[Area],$B$2,Master[Department],$D$2,Master[Active],$F$2)
- this works but it counts the duplication's


Can someone please put these 2 formulas in 1 ( i'm probably missing something)

Also i have 2 employees with the same name but different DOB could these to be also added that their counted separately. If an example of database is require - i will add (need to GDPR it 1st )

[AliGW]

Vista is a version of Windows, not Office - please update your forum profile with the Office version you are using.

There are instructions at the top of the page explaining how to attach your sample workbook.

[samba_ravi]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[newcommer]

See attached - hope it makes sense - i had to remove a lot of tabs charts

all names/dob are false

[AliGW]

Are you still using Excel 2010? If not, please update your profile. If you have MS365, this becomes much easier ...

[newcommer]

updated to say 2010

[AliGW]

It already said Excel 2010 before, so are you saying that this is still the version you are using and that you have not updated to something newer?

[newcommer]

Yes, on the PC that i work with within work is 2010

[JeteMc]

As to counting distinct employee names/DOB in cell O6, try: =SUMPRODUCT(1/COUNTIFS(E3:E7,E3:E7,F3:F7,F3:F7))
That said why not just count the distinct ID's?
As to getting a distinct count by area, department and active, I suppose it could be done using 3 helper columns, but it seems easier to do using a pivot table and slicers as modeled in the attached file.
Let us know if you have any questions.

[newcommer]

Thanks JeteMc, but i should of left of the ID's as these are not distinct ID'd - there are employees without IDs so this is N/A. without ID's can we sumproduct with out duplicates.
Ive never used the slicer - but spent the weekend learning it currently enjoying it Immensely

[bebo021999]

Is this are you looking for?

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[newcommer]

Thank you bebo021999. this works, expect it calculates an extra 6 in the return. Reason: I have 6 employees that work in 3 different areas. the formula provided returns the correct amount and eliminates the duplicates from the total except for these 6 in the over all total. the formula is below (changed to suit the actual spreadsheet. G2= Active status (all, current, ceased )

{=SUM(IF(Master[Active]=IF(G2="All",Master[Active],G2),IFERROR(1/COUNTIFS(Master[Active],IF(G2="All",Master[Active],G2),Master[Full Name],Master[Full Name],Master[Date of Birth],Master[Date of Birth]),0)))}