# COUNTIFS OR...multiple countifs without duplication in the numbers

1. ## COUNTIFS OR...multiple countifs without duplication in the numbers

Hello all,

after spending the afternoon on this one, I resigned and thought I'd bring this to the board.

I have a data input sheet and a summary sheet which uses countifs to report on month end numbers. I have everything figured out except my final column. I need to know how many applications have either an applicant or the joint applicant aged 60+.

The client's age and the joint client's age are recoreded in two separate columns.

Can I use a combination of a countifs and an OR formula? or is the SUMPRODUCT function what I'm after and how?

Countifs OR example.xls

2. ## Re: COUNTIFS OR...multiple countifs without duplication in the numbers

This formula should work:
Formula:
`Please Login or Register  to view this content.`

The formula counts the client column + Joint column - rows that meet BOTH criteria (as they would have been double counted).

3. ## Re: COUNTIFS OR...multiple countifs without duplication in the numbers

Try this formula in 'East Summary'!I4 = COUNTIFS(East!A:A,A4,East!Q:Q,">=60")+COUNTIFS(East!A:A,A4,East!Q:Q,"<60",East!S:S,">=60")

The OR statement is adding two criterias... (if ClientAge>=60) + (if ClientAge<60 and JointAge>=60)

Hope that makes sense...

Let me know otherwise...

Dennis

4. ## Re: COUNTIFS OR...multiple countifs without duplication in the numbers

Thanks Melvinrobb and Dennis.

Worked perfectly!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1