I'm using a sumproduct function to count how many times a combination of 2
criteria occur at the same time across a range.

Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100 .

Im using
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B1000)) which counts how
many occurances of Jan-08 there are with a value of over 0.

I also need to know:

1. how to calculate the total of all the values in Col B when both my
criteria
are met. eg. sum of col B entries when Col A is Jan-08 and Col B has an
entry of any value in it. Any thoughts on how I amend the sumproduct
function to sum rather than count when two criteria are met.

2. Also, by using the 0 in my function, it then doesn't include any 0
entries I might have in Col B. I changed the formula to =0 at the end as
follows
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B100=0))
but it then included the cells that were blank too (I only want them to be
included in my count if I have a numerical value in the cell). Is there a
way of setting the 2nd criteria to be a value or entry of any numerical
value, even zero, perhaps by adding a wildcard*.

Thanks for any help you can offer.

--
KJ

Hi,

Try this.

=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*isnumber(B1:B100))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"KJ" wrote in message
...
I'm using a sumproduct function to count how many times a combination of 2
criteria occur at the same time across a range.

Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100 .

Im using
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B1000)) which counts
how
many occurances of Jan-08 there are with a value of over 0.

I also need to know:

1. how to calculate the total of all the values in Col B when both my
criteria
are met. eg. sum of col B entries when Col A is Jan-08 and Col B has an
entry of any value in it. Any thoughts on how I amend the sumproduct
function to sum rather than count when two criteria are met.

2. Also, by using the 0 in my function, it then doesn't include any 0
entries I might have in Col B. I changed the formula to =0 at the end as
follows
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B100=0))
but it then included the cells that were blank too (I only want them to be
included in my count if I have a numerical value in the cell). Is there a
way of setting the 2nd criteria to be a value or entry of any numerical
value, even zero, perhaps by adding a wildcard*.

Thanks for any help you can offer.

--
KJ

Thanks - that sorts the problem of replacignthe zero to look for any
numerical value.

Any thoughts on the 1st point about calculating the sum total? Thx.
--
KJ


"Ashish Mathur" wrote:

Hi,

Try this.

=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*isnumber(B1:B100))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"KJ" wrote in message
...
I'm using a sumproduct function to count how many times a combination of 2
criteria occur at the same time across a range.

Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100 .

Im using
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B1000)) which counts
how
many occurances of Jan-08 there are with a value of over 0.

I also need to know:

1. how to calculate the total of all the values in Col B when both my
criteria
are met. eg. sum of col B entries when Col A is Jan-08 and Col B has an
entry of any value in it. Any thoughts on how I amend the sumproduct
function to sum rather than count when two criteria are met.

2. Also, by using the 0 in my function, it then doesn't include any 0
entries I might have in Col B. I changed the formula to =0 at the end as
follows
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B100=0))
but it then included the cells that were blank too (I only want them to be
included in my count if I have a numerical value in the cell). Is there a
way of setting the 2nd criteria to be a value or entry of any numerical
value, even zero, perhaps by adding a wildcard*.

Thanks for any help you can offer.

--
KJ

Hi,

Question 1: Sum
=SUMPRODUCT(--(TEXT(A1:A100,"mmm-yy")="Jan-08"),--(B1:B1000),B1:B100)

Question 2: Count =0
=SUMPRODUCT(--(TEXT(A1:A100,"mmm-yy")="Jan-08"),--(B1:B100=0),--ISNUMBER(B1:B100))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"KJ" wrote:

I'm using a sumproduct function to count how many times a combination of 2
criteria occur at the same time across a range.

Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100 .

Im using
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B1000)) which counts how
many occurances of Jan-08 there are with a value of over 0.

I also need to know:

1. how to calculate the total of all the values in Col B when both my
criteria
are met. eg. sum of col B entries when Col A is Jan-08 and Col B has an
entry of any value in it. Any thoughts on how I amend the sumproduct
function to sum rather than count when two criteria are met.

2. Also, by using the 0 in my function, it then doesn't include any 0
entries I might have in Col B. I changed the formula to =0 at the end as
follows
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B100=0))
but it then included the cells that were blank too (I only want them to be
included in my count if I have a numerical value in the cell). Is there a
way of setting the 2nd criteria to be a value or entry of any numerical
value, even zero, perhaps by adding a wildcard*.

Thanks for any help you can offer.

--
KJ

Hi Ashish,

I think this fails if B1 contains a negative number.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Ashish Mathur" wrote:

Hi,

Try this.

=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*isnumber(B1:B100))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"KJ" wrote in message
...
I'm using a sumproduct function to count how many times a combination of 2
criteria occur at the same time across a range.

Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100 .

Im using
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B1000)) which counts
how
many occurances of Jan-08 there are with a value of over 0.

I also need to know:

1. how to calculate the total of all the values in Col B when both my
criteria
are met. eg. sum of col B entries when Col A is Jan-08 and Col B has an
entry of any value in it. Any thoughts on how I amend the sumproduct
function to sum rather than count when two criteria are met.

2. Also, by using the 0 in my function, it then doesn't include any 0
entries I might have in Col B. I changed the formula to =0 at the end as
follows
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B100=0))
but it then included the cells that were blank too (I only want them to be
included in my count if I have a numerical value in the cell). Is there a
way of setting the 2nd criteria to be a value or entry of any numerical
value, even zero, perhaps by adding a wildcard*.

Thanks for any help you can offer.

--
KJ

Hi,

I wonder why - I am checking for a number.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Shane Devenshire" wrote in
message ...
Hi Ashish,

I think this fails if B1 contains a negative number.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Ashish Mathur" wrote:

Hi,

Try this.

=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*isnumber(B1:B100))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"KJ" wrote in message
...
I'm using a sumproduct function to count how many times a combination
of 2
criteria occur at the same time across a range.

Col A contains month and year and is custom formatted as mmm-yy eg
Jan-08
Col B contains a numerical value eg £100 .

Im using
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B1000)) which counts
how
many occurances of Jan-08 there are with a value of over 0.

I also need to know:

1. how to calculate the total of all the values in Col B when both my
criteria
are met. eg. sum of col B entries when Col A is Jan-08 and Col B has an
entry of any value in it. Any thoughts on how I amend the sumproduct
function to sum rather than count when two criteria are met.

2. Also, by using the 0 in my function, it then doesn't include any 0
entries I might have in Col B. I changed the formula to =0 at the end
as
follows
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B100=0))
but it then included the cells that were blank too (I only want them to
be
included in my count if I have a numerical value in the cell). Is
there a
way of setting the 2nd criteria to be a value or entry of any numerical
value, even zero, perhaps by adding a wildcard*.

Thanks for any help you can offer.

--
KJ