Sumproduct works over a range of data; my answer will be generic because it
isn't clear whether your have one sheet per "response", or if the values in
A3 and B3 are repeated (for new values) across new columns, or new rows, or
whatever.
Sumproduct works with many conditions. Example:
Sumproduct ((A1:A1000= "Chippy")*1,(B1:B1000="No")*1, (C1:C1000 > 100)*1)
you will see some people preface the sumproduct statements with a double
negative, others multiply the result of each statement by 1 to force the
returned value to be numeric.
-Make sure that your ranges are all the same size
-I don't recall offhand if sumproduct is one of them, but some formulas
don't play well with a full column reference (C:C) so I tend to use fixed row
numbers by habit (C2:C10000)
-I don't recall offhand how sumproduct interacts with error codes, but my
guess is that it will not return a value if any of the component cells have a
value of DIV#0, #N/A, etc.
-To get your total (your denominator) just remove the component of the
sumproduct that counts that element, e.g.
Sumproduct ((A1:A1000= "Chippy")*1, (C1:C1000 > 100)*1)
If you want to tally completely separate criteria, use AND, OR statements
Sumproduct (OR(AND((A1:A1000= "Chippy")*1,(B1:B1000="No")*1),AND((A1:A1000=
"Oreo")*1,(B1:B1000="Yes")*1)))
(aircode, I probably don't have the right number of parans, but this gives
the basic idea)
HTH,
Keith
"Craig" wrote:
> Hi,
>
> Following a post I did yesterday 'Count Text to get a Percentage'....my next
> post is that I need to count text entries in differing cells to get a result
> - Probably Sumproduct is the probably the answer but despite reading up can't
> get it.
>
> In Cell A1 (Title Merged across): Have you Carried out a H&S Check?
> In Cell A2: Select Job Title
> In Cell A3: Is the list (Validation List), Plasterer, Chippy, Brickie
> In Cell B3: Answer = Yes or No (Validation)
>
> Q: If I get: Chippy (A3) and Yes (B3)......how can I sum this to get a
> figure? to the effect that every Chippy that answers Yes = (a Number) based
> on the two answers.......and every Chippy that answers No = (a Number) based
> on the two answers?
>
> Q: I will need to get a percentage from this eventually i.e. the total
> number of Chippy's that answer Yes = (A Number), Total number of Chippy's
> that answer No = (A Number) against the total number of Chippy's...which is I
> believe will be:
>
> Total Chippy who answered Yes(divided by)Total Chippy = Answer
>
> Also is it possible to sum more than two Text Answers....having looked at
> this discussion group...there only ever seems to be two parts to a
> 'Sumproduct'...I assume(wrong to assume I know..but) there must be more that
> two?
>
> Sorry if my post is too lengthy...got deadline...haven't we all !!!!
>
> Thanks in anticipation
>
> Craig
|
Similar Threads
