Re: Sum Count of Single Criteria in Multiple Non-Adjacent columns

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Assuming that Columns B, D, F, H, and J are your five non-adjacent
columns, and that you want to count the total number of times the value
'North' is contained in those columns, whether the data is filtered or
not, try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),{0,2,4,
6,8},1))*(T(OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),{0,2,4,6,8},1))="N
orth"))

To change the columns being evaluated, adjust the constant array
{0,2,4,6,8}. The 0 refers to the column being referenced or starting
point, in this case Column B. The 2 refers to the number of columns to
the right, in this case Column D, and so on. Also, adjust the range
accordingly.

Hope this helps!

In article <512DC93916950@xxxxxxxxxxxx>,
"Sam via OfficeKB.com" <forum@xxxxxxxxxxxx> wrote:

> Hi All,
>
> I wish to sum the count of a single TEXT criteria that is located in several
> (5) non-adjacent columns - hundreds of rows. Also, should I choose to apply
> filters: I require the Formula to show the summed count of ONLY Visible
> Filtered cells. How can this best be achieved with minimum calculation /
> processing overhead?
>
> I located this Formula on
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1:$A$100)-ROW
> (INDEX($A$1:$A$100,1,1)),0))=1),--($B$1:$B$100="North"),$A$1:$A$10)
>
> However, I am not sure if it is feasible to reference my 5 non-adjacent
> columns based on the above Formula, perhaps a more suitable solution exists?
>
> Thanks
> Sam
.

Relevant Pages

  • Re: Who invented Dichroic Filters?
    ... These days these filters are indeed made by multilayer coatings, ... but I'm reasonably familiear with dichroic filters used for colour ... Thanks very much for the reference as I have always been fascinated by ... If you want more references on Lippmann photography, ...
    (sci.optics)
  • Re: DirectShow graph releases my object once too many
    ... > While watching object reference counts, it appears that a call to ... > several custom intermediary filters, ... (Otherwise, don't expect a followup) ...
    (microsoft.public.win32.programmer.directx.video)
  • Re: when adding a filter to the graph, does it reference count increas
    ... > need to explicitly remove filters from the graph when closing a graph? ... Just use CComPtr and CComQIPtr and let the filters get released ... Then you don't have to worry about the reference count. ... (Otherwise, don't expect a followup) ...
    (microsoft.public.win32.programmer.directx.video)
  • Re: Good fast tool for adjusting contrast/levels
    ... I followed your advice but the filters don't seem to work. ... with a small video still underneath and then on the right I get ... >>I have some quicktime movies I want to boost the contrast/brightness on. ... > From there you can also see RGB adjustments, HSB adjust, alpha gain ...
    (uk.rec.video.digital)
  • Re: BiQuad frequency response for visualisation
    ... symmetry in EQ filters, ... This applies to the EQ sections as well as to the tone control sections. ... With EQ sections, we have to adjust Q by gain, and with the shelving sections, we have to adjust the corner frequencies. ...
    (comp.dsp)