Use of AND in work sheet

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I'm trying to use the AND worksheet function but I can't seem to get it
to work. I only what to sum something when it meets certain criterion.

Formula below:

=SUM(SUMIF('raw stats'!$C$1:$C$500, AND('raw stats'!$C$1:$C$500 = B3,
'raw stats'!$A$1:$A$500 = "timeDH", 'raw stats'!$B$1:$B$500 =0),'raw
stats'!$D$1:$D$500)+SUMIF('raw stats'!$C$211:$C$226,B3,'raw
stats'!$D$211:$D$226)) / SUM(COUNTIF('raw
stats'!$C$171:$C$186,B3)+COUNTIF('raw stats'!$C$211:$C$226,B3))

What I'm trying to do is make the following that does what I want it
too so that I don't have to change ranges all the time and have to
figure out the ranges, i.e. where certain row id's start and end:
=SUM(SUMIF('raw stats'!$C$171:$C$186,B3,'raw
stats'!$D$171:$D$186)+SUMIF('raw stats'!$C$211:$C$226,B3,'raw
stats'!$D$211:$D$226)) / SUM(COUNTIF('raw
stats'!$C$171:$C$186,B3)+COUNTIF('raw stats'!$C$211:$C$226,B3))

Any thoughts?

Thanks,

Frank
 
For this I would use a sumproduct formula similar (but longer) than this;

=+SUMPRODUCT((D11:D15=B3)*(F11:F15="Time DH"),E11:E15)

... that would be similar to the one you want, but yours would be longer. It
evaluates line by line; if D11 = B3 and (the * = and) F11="Time DH" and
E11=23, the answer will be 23.
If those first 2 conditions are met on row 12, with E12=2, the answer will
be 25.
 
Frank said:
I'm trying to use the AND worksheet function but I can't seem to get it
to work. I only what to sum something when it meets certain criterion.

Formula below:

=SUM(SUMIF('raw stats'!$C$1:$C$500, AND('raw stats'!$C$1:$C$500 = B3,
'raw stats'!$A$1:$A$500 = "timeDH", 'raw stats'!$B$1:$B$500 =0),'raw
stats'!$D$1:$D$500)+SUMIF('raw stats'!$C$211:$C$226,B3,'raw
stats'!$D$211:$D$226)) / SUM(COUNTIF('raw
stats'!$C$171:$C$186,B3)+COUNTIF('raw stats'!$C$211:$C$226,B3))

What I'm trying to do is make the following that does what I want it
too so that I don't have to change ranges all the time and have to
figure out the ranges, i.e. where certain row id's start and end:
=SUM(SUMIF('raw stats'!$C$171:$C$186,B3,'raw
stats'!$D$171:$D$186)+SUMIF('raw stats'!$C$211:$C$226,B3,'raw
stats'!$D$211:$D$226)) / SUM(COUNTIF('raw
stats'!$C$171:$C$186,B3)+COUNTIF('raw stats'!$C$211:$C$226,B3))

Any thoughts?

Thanks,

Frank
Click to expand...

I can't see exactly what you are trying to do. However, it's sufficient for
a start to say that SUMIF can only take a simple range as its first
argument, not a function of a range or ranges. Also, AND will only return a
single TRUE/FALSE result, not an array.

Take a look at SUMPRODUCT, along these lines:
=SUMPRODUCT(('raw stats'!$C$1:$C$500=B3)*('raw
stats'!$A$1:$A$500="timeDH")*('raw stats'!B$1:$B$500 =0),'raw
stats'!$D$1:$D$500)
 
Thanks that did the trick!!!!
For this I would use a sumproduct formula similar (but longer) than this;

=+SUMPRODUCT((D11:D15=B3)*(F11:F15="Time DH"),E11:E15)

.. that would be similar to the one you want, but yours would be longer. It
evaluates line by line; if D11 = B3 and (the * = and) F11="Time DH" and
E11=23, the answer will be 23.
If those first 2 conditions are met on row 12, with E12=2, the answer will
be 25.
Click to expand...
 
I'm trying to use the AND worksheet function but I can't seem to get it
to work. I only what to sum something when it meets certain criterion.
Click to expand...
...

You can't use multiple criteria with SUMIF or COUNTIF, so AND won't work in the
criteria argument of either. You need to use other functions. SUMPRODUCT is
generally recommended, e.g.,

=SUMPRODUCT((B2:B9>=3)*(C2:C9="foo")) 2 criteria 'COUNTIF'

=SUMPRODUCT((B2:B9>=3)*(C2:C9="foo")*D2:D9) 2 criteria 'SUMIF'
 
Yes, there's something wrong there. You don't need COUNTIF in there, Sumproduct will take over what COUNTIF would be doing.

you can refer above, where I wrote one short sumproduct formula (or Harlan or Pauls) that explains what sumproduct will do. Again you don't want COUNTIF in there. One change now that I understand what you're looking for better, is

SUMPRODUCT((D11:D15=B3)*(F11:F15=E2)*(E11:E15=G2))

which is doing nothing more than (effectively) counting each time those criteria are met on a single line. the other formula from before, would have summed a range if 2 criteria were met.


Thanks, I tried sumproduct as I though too that would work but it didn't. I must have done something wrong then

=COUNTIF('raw stats'!$A$1:$A$500,SUMPRODUCT(('raw stats'!$C$1:$C$500=B3)*('raw stats'!$A$1:$A$500=E2)*('raw stats'!$B$1:$B$500=D2)))


Dave R. wrote:

Frank, your question has been answered already (use sumproduct). Please
study the suggestions already given before asking the question again; and if
you are going to ask for clarification, ask it in the same thread.

Was trying to get countif to work with criteria but no luck. What I
want to do is get the number of rows that have 3 certain values. i.e.
the values in b3, e2, d2 to all match then count that line.



=COUNTIF('raw stats'!$A$1:$A$500,('raw stats'!$C$1:$C$500=B3)+('raw
stats'!$A$1:$A$500=E2)+('raw stats'!$B$1:$B$500=D2))
 
Back
Top