Count Function????

[coccio]

I want to be able to count how many up figure and down figures in a range

Like this example:

In column A: I have the Month numbered 1-12
In column B: I have the days numbered 1-31
In column C: I have the years from 1988-2003
In column D: are the numbers ranging from positive to negative.

I want to be able to for example find:


12th month in (column A) and the fist day (Column B) and count how many Column D numbers are up. And also how many are down.

How can that be done?

 

[Aladin Akyurek]

Here is the formula in the roll up:

=SUMPRODUCT(--('Weekly Activities'!$B$7:$B$85=2),--('Weekly Activities'!$C$7:$C$85=prospect))

Column a has names, b has numbers, c has type of call. I am trying to count column a given 2 conditions in b and c.

Thanks again,

=SUMPRODUCT(--('Weekly Activities'!$B$7:$B$85=2),--('Weekly Activities'!$C$7:$C$85="prospect"))

 

[lewis_a1]

Aladin, thank you that worked! Much appreciated!

 

[lewis_a1]

I have a few questions, sorry if they seem stupid - I am an excel rookie...

I have a data spreadsheet which i would like to sum the $ amount in cell e, given the following conditions: in cell d (DDA), in cell h (1), and cell f (closed).

I have tried sumproduct and sumif to no avail.

Any feedback would be greatly appreciated

On another note, how does one create a menu drop down list (like product a, b, c) which can be selected in a field? Does this involve setting up a macro? Any help would be appreciated!!!

 

[lewis_a1]

This is the formula I attempted: =
IF(Liabilities!H7:H81="1"),--IF(Liabilities!E7:E81="DDA"),-- IF(Liabilities!F7:F81="closed"), SUM(Liabilities!E7:E81)

 

[Aladin Akyurek]

This is the formula I attempted: =
IF(Liabilities!H7:H81="1"),--IF(Liabilities!E7:E81="DDA"),-- IF(Liabilities!F7:F81="closed"), SUM(Liabilities!E7:E81)

=SUMPRODUC(--(Liabilities!$H$7:$H$81=1),--(Liabilities!$D$7:$D$81="DDA"),--(Liabilities!$F$7:$F$81="closed"),Liabilities!$E$7:$E$81)

 

[lewis_a1]

That worked flawlessly, thank you.

I tried to reference a cell which contains May (month) and the formula wont return what I was trying to do. For example, in the following formula, the section "May" I would like to reference a cell which contains the month so that I dont have to change the formulas monthly

=SUMPRODUCT(--(Assets!$H$7:$H$81=1),--(Assets!$D$7:$D$81="Lease"),--(Assets!$F$7:$F$81="funding"),--(Assets!$G$7:$G$81="MAY"),Assets!$E$7:$E$81)

Can I reference for example B6, which contains the month to return this condition? If so, do you have any ideas?

Also, I was trying to use the IF comand to return Q1, Q2, Q3 and Q4 based on the month and am getting an error when I enter more than one condition. Here is the formula I used =IF(G8="December","Q4")

Is there another formula that can be used for multiple conditions?

Thanks again for all your help! I owe you one!

 

[Aladin Akyurek]

That worked flawlessly, thank you.

You're welcome.

I tried to reference a cell which contains May (month) and the formula wont return what I was trying to do. For example, in the following formula, the section "May" I would like to reference a cell which contains the month so that I dont have to change the formulas monthly

=SUMPRODUCT(--(Assets!$H$7:$H$81=1),--(Assets!$D$7:$D$81="Lease"),--(Assets!$F$7:$F$81="funding"),--(Assets!$G$7:$G$81="MAY"),Assets!$E$7:$E$81)

Can I reference for example B6, which contains the month to return this condition? If so, do you have any ideas?

Using cell refs for conditions is OK and well-advised. Does G7:G81 house (a) true dates or (b) just month names? If (a), then B6 should house a first day date like:

1-May-06

The formula would then become:

=SUMPRODUCT(--(Assets!$H$7:$H$81=1),--(Assets!$D$7:$D$81="Lease"),--(Assets!$F$7:$F$81="funding"),--(Assets!$G$7:$G$81-DAY(Assets!$G$7:$G$81)+1=B6),Assets!$E$7:$E$81)

If (b), you probably have stray space chars around source entries. You can remove them with the TrimAll macro for the formula with MAY to work. The macro code is available in some of the threads on this board.

Also, I was trying to use the IF comand to return Q1, Q2, Q3 and Q4 based on the month and am getting an error when I enter more than one condition. Here is the formula I used =IF(G8="December","Q4")

I assume this is a separate question. You can invoke...

Either:

="Q"&ROUNDUP(MONTH("1-"&G8)/3,0)

Or:

=LOOKUP(G8,{"January";"February";"March";"April";"May";
                       "June";"July";"August";"September";"October";
                       "November";"December"},
                    {"Q1";"Q1";"Q1";"Q2";"Q2";"Q2";
                        "Q3";"Q3";"Q3";"Q4";"Q4";"Q4"})

Is there another formula that can be used for multiple conditions?

Other than one with SumProduct? The answer is yes, e.g., the formulas that you can create with Conditional Sum Wizard.

 

[lewis_a1]

Hi Aladin, B6 contains "May"BG is actually a seperate page. Here is the formula I tried: =SUMPRODUCT(--(Assets!$H$7:$H$81=1),--(Assets!$D$7:$D$81="Lease"),--(Assets!$F$7:$F$81="funding"),--(Assets!$G$7:$G$81-DAY(Assets!$G$7:$G$81)+1=Start!B6),Assets!$E$7:$E$81)

and it returned #VALUE! Did I miss something? Thanks again,

 

[Aladin Akyurek]

Hi Aladin, B6 contains "May"BG is actually a seperate page. Here is the formula I tried: =SUMPRODUCT(--(Assets!$H$7:$H$81=1),--(Assets!$D$7:$D$81="Lease"),--(Assets!$F$7:$F$81="funding"),--(Assets!$G$7:$G$81-DAY(Assets!$G$7:$G$81)+1=Start!B6),Assets!$E$7:$E$81)

and it returned #VALUE! Did I miss something? Thanks again,

If Assets!$G$7:$G$81 houses month names, not dates, then you just need:

=SUMPRODUCT(--(Assets!$H$7:$H$81=1),--(Assets!$D$7:$D$81="Lease"),--(Assets!$F$7:$F$81="funding"),--(Assets!$G$7:$G$81=Start!B6),Assets!$E$7:$E$81)

 

[sucett]

if my data is in a different sheet i need to use the inderect formula to make reference to that sheet in order for the formula to work?

here is the formula:

=SUMPRODUCT(--(INDIRECT("West!$C$5:$C$50"):INDIRECT("West!$C$5:$C$50")=Total!$A$1),--(INDIRECT("West!$e$5:$e$50"):INDIRECT("West!$e$5:$e$50")=Total!$A4))

in the sheet west is the data that i need to count
but in the sheet total are the conditions for the count area.

i tryied using the formula without the indirect function but it didnt work.. by adding the inderect function it work. Do you know why??

thanks!

Expensiveness in ascending order for the oft-used coercers:

--

+0

*1

Plus Zero is closer to double Nots.

Note that all math operations cause coercion lke in

=TRUE^10

Recall also that the syntax for SumProduct is...

SumProduct(X1,X2,...)

where each X must be either a numerical range or a numerical array object.

Given the above syntax,

=SUMPRODUCT(A2:A10,B2:B10)

is faster than

{=SUM(A2:A10*B2:B10)}

but also faster than

=SUMPRODUCT(A2:A10*B2:B10)

where both A2:A10and B2:B10are numerical range objects.

In multiconditional computations (say multiconditional counting), we have often logical array objects to consider, e.g.,

E2:E10="UP" and F2:F10=2003

Lets name such expressions conditionals.

[1]

{=SUM((E2:E10="UP")*(F2:F10=2003))}

would give the desired multiconditional count as would:

[2]

=SUMPRODUCT((E2:E10="UP")*(F2:F10=2003))

[3]

=SUMPRODUCT(--(E2:E10="UP"),--(F2:F10=2003))

[4]

=SUMPRODUCT((E2:E10="UP")+0,(F2:F10=2003)+0)

[5]

=SUMPRODUCT((E2:E10="UP")*1,(F2:F10=2003)*1)

In [1] and [2], the coercion occurs as a side-effect of a mathematical operation (a multiplication as indicated by *).

In [3] to [5], we first coercethe arrays of logical values as result of the conditional evaluations into numerical arrays (as SumProduct needs/expects them), which then get multiplied by SumProduct (the comma in the syntax of SumProduct stands for "vector multiplication") then summed.

The timing results show that coercion by -- leads to less jumpy computations. That is, each run shows the same timing characteristics, compared to a coercion with +0 (coercion with *1 is slower). Average time are almost the same for SumProduct formulas that use these coercers.

The SumProduct formulas would be much faster if SumProduct would implicitly coerce (that is, without the need for an explicit coercer like -- or +0) the results of conditional evaluations into numbers.