• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with CountIf/Sumproduct with multiple arrays

DavidHimmel90

New Member
Hello,

I've seen a few answers on this, but none that seem to work for me. Please forgive any formatting issues -- I am new to forums!

I have a set of data from a survey similar to below:

Respondents Question 1: How would you rate your various pizzas? Question 2: How would you describe your approach to pizza making?
Column A Respondents Column B 1A -- Cheese Column C 1B -- Bacon Column D 1C -- Garlic Column E Question 2: How would you describe your approach to pizza making?
John Fair Poor Very Good Innovative
Jack Good Excellent Very Good Innovative
Joe Poor Excellent Excellent Follower

I want to analyze the responses to Question 1 by the responses in Question 2 (e.g. total # of "Fair" and "Poor" responses from Pizza Makers who described themselves as "Innovative"). I have been trying to use formulas like below:

=SUMPRODUCT((B:D="Fair")*(E:E="Innovative"))+SUMPRODUCT((B:D="Poor")*(E:E="Innovative")) [NOTE used as an array formula).

This is giving me a "resources not available" error. Can anyone help me out here?
 
David

Firstly, Welcome to the Chandoo.org Forums

Why don't you put the table into a Pivot Table
then do a count by a field

I'm not sure about the use of columns B:D in your formula,
I'd suggest uploading a sample file so we can better assist you
 
Hi ,

Two points :

1. The error message is most probably because you have used full column references ; use proper range references such as $B$2:$D$100.

2. The formula itself will give wrong results , since the first row will be counted twice ; the word Fair occurs in column B , while the word Poor occurs in column C , unless this is what you want.

Narayan
 
Back
Top