Nested Countif Formula Question

Thanks:  0
Likes:  0

# Thread: Nested Countif Formula Question

1. ## Nested Countif Formula Question

Hello Forum. I am working on a nested countif formula, however I can't get it together and would like to see if anyone could lend me a hand in figuring it out. Below is a the data between two sheets. The formula that I am trying to write would give me the result on Sheet1, Col D, calculating the Total for Jon at 2. I have tried to nest Countif's, Sumif's and Vlookup's with no success. Thank you for taking the time to read my post and any reply would be greatly appreciated.

 Sheet1 Col A Col B Col C Col D Name: Nbr: Nbr: Total: Jon 225 Jan-11 2

 Sheet2 Col A Col B Col C Name: Nbr: Service: Jon 225 Jan-11 Bob 110 Jan-11 Sam 440 Jan-11 Jon 225 Jan-11 Bob 110 Feb-11 Sam 440 Feb-11 Jon 225 Dec-10 Sam 440 Dec-10

2. ## Re: Nested Countif Formula Question

Sheet2

 * A B C 1 Name: Nbr: Service: 2 Jon 225 Jan-11 3 Bob 110 Jan-11 4 Sam 440 Jan-11 5 Jon 225 Jan-11 6 Bob 110 Feb-11 7 Sam 440 Feb-11 8 Jon 225 Dec-10 9 Sam 440 Dec-10

Sheet1

 * A B C D 1 Name: Nbr: Nbr: Total 2 Jon 225 Jan-11 2

 Cell Formula D2 =SUMPRODUCT(--(Name=A2),--(Nbr=B2),--(Service=Sheet1!C2))
Names in Formulas
 Cell Name Applies to D2 Name =OFFSET(Sheet2!\$A\$1,1,0,COUNTA(Sheet2!\$A:\$A)-1,1) D2 Nbr =OFFSET(Name,0,1) D2 Service =OFFSET(Name,0,2)

Excel tables to the web >> Excel Jeanie HTML 4

3. ## Re: Nested Countif Formula Question

Originally Posted by storm925
Hello Forum. I am working on a nested countif formula, however I can't get it together and would like to see if anyone could lend me a hand in figuring it out. Below is a the data between two sheets. The formula that I am trying to write would give me the result on Sheet1, Col D, calculating the Total for Jon at 2. I have tried to nest Countif's, Sumif's and Vlookup's with no success. Thank you for taking the time to read my post and any reply would be greatly appreciated.

 Sheet1 Col A Col B Col C Col D Name: Nbr: Nbr: Total: Jon 225 Jan-11 2

 Sheet2 Col A Col B Col C Name: Nbr: Service: Jon 225 Jan-11 Bob 110 Jan-11 Sam 440 Jan-11 Jon 225 Jan-11 Bob 110 Feb-11 Sam 440 Feb-11 Jon 225 Dec-10 Sam 440 Dec-10
Try this...

=SUMPRODUCT(--(Sheet2!A2:A9=A2),--(Sheet2!B2:B9=B2),--(YEAR(Sheet2!C2:C9)=YEAR(C2)),--(MONTH(Sheet2!C2:C9)=MONTH(C2)))

4. ## Re: Nested Countif Formula Question

Thank you njimack, it took me a little while to figure it out... works like a charm. I never thought of using "sumproduct" with "offset". Five Stars!
--Ben

5. ## Re: Nested Countif Formula Question

Hi #NAME?,

I like the simplicity of your formula, however I get a #NUM! error message.
Is there something missing?

--Ben

6. ## Re: Nested Countif Formula Question

if you have excel 2007 or higher, you can use COUNTIFS as well.

Sheet2
ABCD
1Sheet1
2Col ACol BCol CCol D
3Name:Nbr:Nbr:Total:
4Jon22511-Jan2
5
6
7Sheet2
8Col ACol BCol C
9Name:Nbr:Service:
10Jon22511-Jan
11Bob11011-Jan
12Sam44011-Jan
13Jon22511-Jan
14Bob11011-Feb
15Sam44011-Feb
16Jon22510-Dec
17Sam44010-Dec
Excel 2007

Worksheet Formulas
CellFormula
D4=COUNTIFS(\$A\$10:\$A\$17,"="&A4,\$B\$10:\$B\$17,"="&B4,\$C\$10:\$C\$17,"="&C4)

7. ## Re: Nested Countif Formula Question

Originally Posted by storm925
Hi #NAME?,

I like the simplicity of your formula, however I get a #NUM! error message.
Is there something missing?

--Ben
Are you using entire columns as range references?

What version of Excel are you using?

You can't use entire columns as range references in the SUMPRODUCT function in Excel versions prior to Excel 2007.

On a side note...

Your dates and date criteria are kind of ambiguous. That's why I tested for both year and month number.

8. ## Re: Nested Countif Formula Question

#name?

I used entire columns, so that is the reason why. I'm also using 2003. After correcting this, it worked like a charm. It's also a lot simpler formula to use. Thank you again for your help and keeping it simple. --Ben

9. ## Re: Nested Countif Formula Question

Originally Posted by storm925
#name?

I used entire columns, so that is the reason why. I'm also using 2003. After correcting this, it worked like a charm. It's also a lot simpler formula to use. Thank you again for your help and keeping it simple. --Ben
Good deal. Thanks for the feedback!

## User Tag List

#### Posting Permissions

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