Counting an Array based on a calculation

[HokieLawrence]

I have a large array of data approximately 41 columns wide by about 180 rows
deep. The columns are headed by date information (weekly) and the rows are
resources.

I would like to count values in each row based on conditions of the columns.
Specifically, I'd like to count the number of resources in each week was
above a number that changes based on different criteria for each week.

Ultimately I manually entered in a different COUNTIF function for each week,
but is there a way that I can count based on some kind of function, for
example something along these lines: COUNTIF(A1:A4,A1:A4>0.9*A5)

Hope anyone knows what the heck I'm talking about and might be able to help
me out of this jam.

Thanks,
HokieLawrence

[Biff]

Hi!

As near as I can tell,

COUNTIF(A1:A4,A1:A4>0.9*A5)

translates to:

=SUMPRODUCT(--(A1:A4>A5*0.9))

Biff

>-----Original Message-----
>I have a large array of data approximately 41 columns
wide by about 180 rows
>deep. The columns are headed by date information
(weekly) and the rows are
>resources.
>
>I would like to count values in each row based on
conditions of the columns.
> Specifically, I'd like to count the number of resources
in each week was
>above a number that changes based on different criteria
for each week.
>
>Ultimately I manually entered in a different COUNTIF
function for each week,
>but is there a way that I can count based on some kind of
function, for
>example something along these lines: COUNTIF
(A1:A4,A1:A4>0.9*A5)
>
>Hope anyone knows what the heck I'm talking about and
might be able to help
>me out of this jam.
>
>Thanks,
>HokieLawrence
>.
>

[HokieLawrence]

Biff,

You're my hero. That works like a charm.

Now that I've got them counted, is there also a way to SUM those values if
they meet that same condition?

Also, can you explain how that works? I've never seen the (--( operation
and I'm not sure what it's making Excel do.

Thanks,
HokieLawrence



"Biff" wrote:

> Hi!
>
> As near as I can tell,
>
> COUNTIF(A1:A4,A1:A4>0.9*A5)
>
> translates to:
>
> =SUMPRODUCT(--(A1:A4>A5*0.9))
>
> Biff
>
> >-----Original Message-----
> >I have a large array of data approximately 41 columns
> wide by about 180 rows
> >deep. The columns are headed by date information
> (weekly) and the rows are
> >resources.
> >
> >I would like to count values in each row based on
> conditions of the columns.
> > Specifically, I'd like to count the number of resources
> in each week was
> >above a number that changes based on different criteria
> for each week.
> >
> >Ultimately I manually entered in a different COUNTIF
> function for each week,
> >but is there a way that I can count based on some kind of
> function, for
> >example something along these lines: COUNTIF
> (A1:A4,A1:A4>0.9*A5)
> >
> >Hope anyone knows what the heck I'm talking about and
> might be able to help
> >me out of this jam.
> >
> >Thanks,
> >HokieLawrence
> >.
> >
>

[Dave Peterson]

J.E. McGimpsey explains it all at:
http://www.mcgimpsey.com/excel/doubleneg.html



HokieLawrence wrote:
>
> Biff,
>
> You're my hero. That works like a charm.
>
> Now that I've got them counted, is there also a way to SUM those values if
> they meet that same condition?
>
> Also, can you explain how that works? I've never seen the (--( operation
> and I'm not sure what it's making Excel do.
>
> Thanks,
> HokieLawrence
>
> "Biff" wrote:
>
> > Hi!
> >
> > As near as I can tell,
> >
> > COUNTIF(A1:A4,A1:A4>0.9*A5)
> >
> > translates to:
> >
> > =SUMPRODUCT(--(A1:A4>A5*0.9))
> >
> > Biff
> >
> > >-----Original Message-----
> > >I have a large array of data approximately 41 columns
> > wide by about 180 rows
> > >deep. The columns are headed by date information
> > (weekly) and the rows are
> > >resources.
> > >
> > >I would like to count values in each row based on
> > conditions of the columns.
> > > Specifically, I'd like to count the number of resources
> > in each week was
> > >above a number that changes based on different criteria
> > for each week.
> > >
> > >Ultimately I manually entered in a different COUNTIF
> > function for each week,
> > >but is there a way that I can count based on some kind of
> > function, for
> > >example something along these lines: COUNTIF
> > (A1:A4,A1:A4>0.9*A5)
> > >
> > >Hope anyone knows what the heck I'm talking about and
> > might be able to help
> > >me out of this jam.
> > >
> > >Thanks,
> > >HokieLawrence
> > >.
> > >
> >

--

Dave Peterson

[Ragdyer]

Dave might have overlooked the first part of the question.

Try this:

=SUMPRODUCT(--(A1:A4>A5*0.9),A1:A4)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> J.E. McGimpsey explains it all at:
> http://www.mcgimpsey.com/excel/doubleneg.html
>
>
>
> HokieLawrence wrote:
> >
> > Biff,
> >
> > You're my hero. That works like a charm.
> >
> > Now that I've got them counted, is there also a way to SUM those values
if
> > they meet that same condition?
> >
> > Also, can you explain how that works? I've never seen the (--(
operation
> > and I'm not sure what it's making Excel do.
> >
> > Thanks,
> > HokieLawrence
> >
> > "Biff" wrote:
> >
> > > Hi!
> > >
> > > As near as I can tell,
> > >
> > > COUNTIF(A1:A4,A1:A4>0.9*A5)
> > >
> > > translates to:
> > >
> > > =SUMPRODUCT(--(A1:A4>A5*0.9))
> > >
> > > Biff
> > >
> > > >-----Original Message-----
> > > >I have a large array of data approximately 41 columns
> > > wide by about 180 rows
> > > >deep. The columns are headed by date information
> > > (weekly) and the rows are
> > > >resources.
> > > >
> > > >I would like to count values in each row based on
> > > conditions of the columns.
> > > > Specifically, I'd like to count the number of resources
> > > in each week was
> > > >above a number that changes based on different criteria
> > > for each week.
> > > >
> > > >Ultimately I manually entered in a different COUNTIF
> > > function for each week,
> > > >but is there a way that I can count based on some kind of
> > > function, for
> > > >example something along these lines: COUNTIF
> > > (A1:A4,A1:A4>0.9*A5)
> > > >
> > > >Hope anyone knows what the heck I'm talking about and
> > > might be able to help
> > > >me out of this jam.
> > > >
> > > >Thanks,
> > > >HokieLawrence
> > > >.
> > > >
> > >
>
> --
>
> Dave Peterson

[Dave Peterson]

Actually, I overlooked the complete thread save that last question.

Thanks for answering the real question.


Ragdyer wrote:
>
> Dave might have overlooked the first part of the question.
>
> Try this:
>
> =SUMPRODUCT(--(A1:A4>A5*0.9),A1:A4)
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > J.E. McGimpsey explains it all at:
> > http://www.mcgimpsey.com/excel/doubleneg.html
> >
> >
> >
> > HokieLawrence wrote:
> > >
> > > Biff,
> > >
> > > You're my hero. That works like a charm.
> > >
> > > Now that I've got them counted, is there also a way to SUM those values
> if
> > > they meet that same condition?
> > >
> > > Also, can you explain how that works? I've never seen the (--(
> operation
> > > and I'm not sure what it's making Excel do.
> > >
> > > Thanks,
> > > HokieLawrence
> > >
> > > "Biff" wrote:
> > >
> > > > Hi!
> > > >
> > > > As near as I can tell,
> > > >
> > > > COUNTIF(A1:A4,A1:A4>0.9*A5)
> > > >
> > > > translates to:
> > > >
> > > > =SUMPRODUCT(--(A1:A4>A5*0.9))
> > > >
> > > > Biff
> > > >
> > > > >-----Original Message-----
> > > > >I have a large array of data approximately 41 columns
> > > > wide by about 180 rows
> > > > >deep. The columns are headed by date information
> > > > (weekly) and the rows are
> > > > >resources.
> > > > >
> > > > >I would like to count values in each row based on
> > > > conditions of the columns.
> > > > > Specifically, I'd like to count the number of resources
> > > > in each week was
> > > > >above a number that changes based on different criteria
> > > > for each week.
> > > > >
> > > > >Ultimately I manually entered in a different COUNTIF
> > > > function for each week,
> > > > >but is there a way that I can count based on some kind of
> > > > function, for
> > > > >example something along these lines: COUNTIF
> > > > (A1:A4,A1:A4>0.9*A5)
> > > > >
> > > > >Hope anyone knows what the heck I'm talking about and
> > > > might be able to help
> > > > >me out of this jam.
> > > > >
> > > > >Thanks,
> > > > >HokieLawrence
> > > > >.
> > > > >
> > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[lob]

Dave Peterson wrote:
> Actually, I overlooked the complete thread save that last question.
>
> Thanks for answering the real question.
>
>
> Ragdyer wrote:
> >
> > Dave might have overlooked the first part of the question.
> >
> > Try this:
> >
> > =SUMPRODUCT(--(A1:A4>A5*0.9),A1:A4)
> > --
> > HTH,
> >
> > RD
> >
> >
---------------------------------------------------------------------------
> > Please keep all correspondence within the NewsGroup, so all may
benefit !
> >
---------------------------------------------------------------------------
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > J.E. McGimpsey explains it all at:
> > > http://www.mcgimpsey.com/excel/doubleneg.html
> > >
> > >
> > >
> > > HokieLawrence wrote:
> > > >
> > > > Biff,
> > > >
> > > > You're my hero. That works like a charm.
> > > >
> > > > Now that I've got them counted, is there also a way to SUM
those values
> > if
> > > > they meet that same condition?
> > > >
> > > > Also, can you explain how that works? I've never seen the (--(
> > operation
> > > > and I'm not sure what it's making Excel do.
> > > >
> > > > Thanks,
> > > > HokieLawrence
> > > >
> > > > "Biff" wrote:
> > > >
> > > > > Hi!
> > > > >
> > > > > As near as I can tell,
> > > > >
> > > > > COUNTIF(A1:A4,A1:A4>0.9*A5)
> > > > >
> > > > > translates to:
> > > > >
> > > > > =SUMPRODUCT(--(A1:A4>A5*0.9))
> > > > >
> > > > > Biff
> > > > >
> > > > > >-----Original Message-----
> > > > > >I have a large array of data approximately 41 columns
> > > > > wide by about 180 rows
> > > > > >deep. The columns are headed by date information
> > > > > (weekly) and the rows are
> > > > > >resources.
> > > > > >
> > > > > >I would like to count values in each row based on
> > > > > conditions of the columns.
> > > > > > Specifically, I'd like to count the number of resources
> > > > > in each week was
> > > > > >above a number that changes based on different criteria
> > > > > for each week.
> > > > > >
> > > > > >Ultimately I manually entered in a different COUNTIF
> > > > > function for each week,
> > > > > >but is there a way that I can count based on some kind of
> > > > > function, for
> > > > > >example something along these lines: COUNTIF
> > > > > (A1:A4,A1:A4>0.9*A5)
> > > > > >
> > > > > >Hope anyone knows what the heck I'm talking about and
> > > > > might be able to help
> > > > > >me out of this jam.
> > > > > >
> > > > > >Thanks,
> > > > > >HokieLawrence
> > > > > >.
> > > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson


Hi There

It sounds as if this is related to a question I have.
The area on my spreadsheet that I will enter whole numbers is from V9
to HB172.
A value will be entered in many columns along any given row.
For each one of these values entered, I would like to multiply that
value with a value entered in row 1 for each of the columns. The
solution will be entered in column A and added with all other solutions
in that row.
example:
a10=10 a15=15 a25=25
v1=(1*10)+(7*25) v10=1 v15=0 v25=7
q1=(2*10)+(6*15) q10=2 q15=6 q25=0
r1=(3*10) r10=3 r15=0 r25=0
s1=(5*15) s10=0 s15=5 s25=0

Any suggestions what formula and formatting to put in column 1.

Thanks in advance, Lob

[Dave Peterson]

It sounds like you want something like:

=SUMPRODUCT($v$1:$hb$1,v9:hb9)

as the formula in A9.

And drag down to row 172.

lob wrote:
<<snipped>>
>
> Hi There
>
> It sounds as if this is related to a question I have.
> The area on my spreadsheet that I will enter whole numbers is from V9
> to HB172.
> A value will be entered in many columns along any given row.
> For each one of these values entered, I would like to multiply that
> value with a value entered in row 1 for each of the columns. The
> solution will be entered in column A and added with all other solutions
> in that row.
> example:
> a10=10 a15=15 a25=25
> v1=(1*10)+(7*25) v10=1 v15=0 v25=7
> q1=(2*10)+(6*15) q10=2 q15=6 q25=0
> r1=(3*10) r10=3 r15=0 r25=0
> s1=(5*15) s10=0 s15=5 s25=0
>
> Any suggestions what formula and formatting to put in column 1.
>
> Thanks in advance, Lob

--

Dave Peterson

[lob]

Thanks Dave:
This did not work. I will try to explain in a different way.
Multiply cells v10*v1, w10*w1, x10*x1, y10*x1 (and so on) hb10*hb1 and
have the sum of these values entered in a10.
Multiply cells v11*v1, w11*w1, x11*x1, y11*x1 (and so on) hb11*hb1 and
have the sum of these values entered in a11.
Multiply cells v12*v1, w12*w1, x12*x1, y12*x1 (and so on) hb12*hb1 and
have the sum of these values entered in a12.
(and so on)
(and so on)
Multiply cell v172*v1, w172*w1, x172*x1, y172*x1 (and so on) hb172*hb1
and have the sum of these values entered in a172.
I will be inserting more rows between rows 10 and 171 in the future
when required.
Is this possible to do? As you can likely tell, I am okay with basic
formulas, but get a bit lost when they get complicated.
Thanks, Lob

[Dave Peterson]

What happened when you used the formula? What formula did you use? And where
did you put it?

I'd try a shorter sample:

In A9:
=SUMPRODUCT($B$1:$E$1,B9:E9)
with data in columns B:E.

It worked ok for me with nontext (numbers or empty) in those cells.


lob wrote:
>
> Thanks Dave:
> This did not work. I will try to explain in a different way.
> Multiply cells v10*v1, w10*w1, x10*x1, y10*x1 (and so on) hb10*hb1 and
> have the sum of these values entered in a10.
> Multiply cells v11*v1, w11*w1, x11*x1, y11*x1 (and so on) hb11*hb1 and
> have the sum of these values entered in a11.
> Multiply cells v12*v1, w12*w1, x12*x1, y12*x1 (and so on) hb12*hb1 and
> have the sum of these values entered in a12.
> (and so on)
> (and so on)
> Multiply cell v172*v1, w172*w1, x172*x1, y172*x1 (and so on) hb172*hb1
> and have the sum of these values entered in a172.
> I will be inserting more rows between rows 10 and 171 in the future
> when required.
> Is this possible to do? As you can likely tell, I am okay with basic
> formulas, but get a bit lost when they get complicated.
> Thanks, Lob

--

Dave Peterson

[lob]

Thanks a heap! All works great (even if I dont understand exactly whats
going on)