Issue with sumproduct formula when counting occurances of multiple criteria within 2 columns

[ahalwa]

http://www.mrexcel.com/forum/excel-questions/61648-countif-combined.htmlI have 2 columns of data where I need to count the number of times a value within a range occurs in the same row as a particular value in the second column. I found this post:
http://www.mrexcel.com/forum/excel-questions/61648-countif-combined.html which appears to explain how to do it. I can not get my sumproduct formula to work though.

The formula I am trying is: =SUMPRODUCT((Data!I:I<30)*(Data!K:K="Trent")) where column I in the "Data" tab contains numbers and column K in the "Data" tab contains names. I am getting #N/A. I am using excel 2010.

This is the actual spreadsheethttps://www.dropbox.com/s/awu146m9mn9pz22/NCR Days Open.xlsx. Formula is in B4 of the "Stats" tab.

Thanks you in advance.

 

[ahalwa]

Sorry, the original post I used with a similar issue was: "http://www.mrexcel.com/forum/excel-questions/61648-countif-combined.html"

 

[Firefly2012]

Do you have any #N/A error values in either colmns I or K in the Data tab?

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum
Some points:

1. Avoid references to entire columns, like I:I or K:K, in SUMPRODUCT.
This impairs seriously the performance

2. There are errors (#N/A) in columns J and K at rows 161 and 162
Try to clean up your data.

Otherwise you'll need an array formula like this in B4
=SUM(IF(Data!$I$3:$I$162<30,IF(Data!$K$3:$K$162=B$3,1)))

confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

M.

 

[ahalwa]

Thank you both for the help, it is working now. I do have one question based on your response, Marcelo. What sort of problems will I run into by referencing entire columns? I will be creating a macro to show metrics based on raw data that is generated monthly and the length of the raw data report changes, so referencing the entire column is the only way I know of to compensate for the change in length.

 

[Marcelo Branco]

Thank you both for the help, it is working now. I do have one question based on your response, Marcelo. What sort of problems will I run into by referencing entire columns? I will be creating a macro to show metrics based on raw data that is generated monthly and the length of the raw data report changes, so referencing the entire column is the only way I know of to compensate for the change in length.

1, You can use a dynamic named range (Google!)

or easier

2. Presently your range is K3:K162 and in the future likely will have more, say, 200 rows (or a little bit more) of data.

If so, you can use something like
=SUMPRODUCT((Data!$I3$:I400<30)*(Data!$K$3:$K$400="Trent"))

or safer

=SUMPRODUCT((Data!$I3:$I$400<>"")*(Data!$I3$:$I$400<30)*(Data!$K$3:$K$400="Trent"))

M.

 

[ahalwa]

Awesome! Thanks again, much appreciated.

 

[Marcelo Branco]

Awesome! Thanks again, much appreciated.

You are very welcome!

M.