Count & Array Function

[Scott Halper]

I have the following Dataset

Month Region Amount
Jan East 10
Jan East 7
Jan West 5
Feb South 2
Feb South 1

I am trying to write an array function that will count the number of
sales in January in the East Region.

I have sucessfully been able to write the array function to sum East
in January but just found out that the Count function does not accept
arrays.

Any help would be appreciated.

Thanks,
Scott

 

[Guest]

Try:

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100="East"),(C2:C100))

If dates are actual dates e.g 01/01/07 formatted as "mmm", not text then for
January:

=SUMPRODUCT(--MONTH(A2:A100)=1),--(B2:B100="East"),(C2:C100))

Equally, you can replace the constants e.g 1(or "Jan") and "East" with
cells containing these values.

FYI the "--" changes the TRUE/FALSE conditions to 1/0 so we can do the
arithmetic.

HTH

 

[Scott Halper]

Try:

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100="East"),(C2:C100))

If dates are actual dates e.g 01/01/07 formatted as "mmm", not text then for
January:

=SUMPRODUCT(--MONTH(A2:A100)=1),--(B2:B100="East"),(C2:C100))

Equally, you can replace the constants e.g 1(or "Jan") and "East" with
cells containing these values.

FYI the "--" changes the TRUE/FALSE conditions to 1/0 so we can do the
arithmetic.

HTH










- Show quoted text -

Thanks, but when I tried your formula, the result is 17 which is the
sum of the two sales in January and I am looking for the number 2.
Any help on that?

 

[Dave Peterson]

remove the (c2:c100) from the formula.

Toppers thought that you wanted the Amount field summed--not the count of sales.

 

[Guest]

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100="East"))