Count & Array Function

S

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
 
T

Toppers

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
 
S

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?
 
D

Dave Peterson

remove the (c2:c100) from the formula.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top