Sumproduct & Count formula
 

Here is my formula
=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(COUNT(G3 :G52)))
Column G contains either text or numbers.
What I want to achieve is for the formula to count how many cells in column
G contain a number if Column A contains a 3, a 4 or a 5.
What I'm getting is the count of Column A multipling the Sum of Column G.
And just so you know, Column G has to be able to contain Text.

It truly is doing my head in!

Sumproduct & Count formula
 

I assume that ColA is a named range covering the same number of cells
as G3:G52, i.e. A3:A52.

If so, then you could approach it this way:

=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(ISNUMBER (G3:G52)))

Hope this helps.

Pete

On Dec 5, 12:27*am, VickiMc wrote:
Here is my formula
=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(COUNT(G3 :G52)))
Column G contains either text or numbers.
What I want to achieve is for the formula to count how many cells in column
G contain a number if Column A contains a 3, a 4 or a 5.
What I'm getting is the count of Column A multipling the Sum of Column G.
And just so you know, Column G has to be able to contain Text.

It truly is doing my head in!

Sumproduct & Count formula
 

On Dec 4, 4:27*pm, VickiMc wrote:
Here is my formula
=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(COUNT(G3 :G52)))
Column G contains either text or numbers.
What I want to achieve is for the formula to count how many
cells in column G contain a number if Column A contains a
3, a 4 or a 5.

Not sure whether ColA is a named range or a single cell. Just to be
clear, does one of the following work for you?

=sumproduct((A1={3,4,5})*isnumber(G3:G52))

=sumproduct((A3:A52={3,4,5})*isnumber(G3:G52))

Sumproduct & Count formula
 

It's always the obvious, isn't it!
Champion! Thanks Pete.
Just Gotta Love this site!

"Pete_UK" wrote:

I assume that ColA is a named range covering the same number of cells
as G3:G52, i.e. A3:A52.

If so, then you could approach it this way:

=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(ISNUMBER (G3:G52)))

Hope this helps.

Pete

On Dec 5, 12:27 am, VickiMc wrote:
Here is my formula
=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(COUNT(G3 :G52)))
Column G contains either text or numbers.
What I want to achieve is for the formula to count how many cells in column
G contain a number if Column A contains a 3, a 4 or a 5.
What I'm getting is the count of Column A multipling the Sum of Column G.
And just so you know, Column G has to be able to contain Text.

It truly is doing my head in!

Sumproduct & Count formula
 

You're welcome, Vicki - glad to be of help.

Pete

On Dec 5, 12:50*am, VickiMc wrote:
It's always the obvious, isn't it!
Champion! Thanks Pete.
Just Gotta Love this site!



"Pete_UK" wrote:
I assume that ColA is a named range covering the same number of cells
as G3:G52, i.e. A3:A52.

If so, then you could approach it this way:

=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(ISNUMBER (G3:G52)))

Hope this helps.

Pete

On Dec 5, 12:27 am, VickiMc wrote:
Here is my formula
=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(COUNT(G3 :G52)))
Column G contains either text or numbers.
What I want to achieve is for the formula to count how many cells in column
G contain a number if Column A contains a 3, a 4 or a 5.
What I'm getting is the count of Column A multipling the Sum of Column G.
And just so you know, Column G has to be able to contain Text.

It truly is doing my head in!- Hide quoted text -

- Show quoted text -

Sumproduct & Count formula
 

That one works equally as well as Petes, though I must admit it looks a lot
tidier than my original.

For my next problem - whilst I have your attention -
my formula is
=SUMPRODUCT((ColA<7)*($D$3:$D52="y")*(COUNT($G$3:$ G52)))
What I want it to do is only sum the cells in Col G if it meets the criteria
of being less than 7 in Column A, and Column D contains a Y.
What it is doing is evaluating to a 1 for ColA, a 1 for ColD and 23 (being
the total count of cells for Column G) for a total of 46. (1+1*23=46).
What it should be giving me is 20, there are 2 items that fit the Criteria
of <7, and "Y", and each have a figure of 10 in Column G.

"joeu2004" wrote:

On Dec 4, 4:27 pm, VickiMc wrote:
Here is my formula
=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(COUNT(G3 :G52)))
Column G contains either text or numbers.
What I want to achieve is for the formula to count how many
cells in column G contain a number if Column A contains a
3, a 4 or a 5.

Not sure whether ColA is a named range or a single cell. Just to be
clear, does one of the following work for you?

=sumproduct((A1={3,4,5})*isnumber(G3:G52))

=sumproduct((A3:A52={3,4,5})*isnumber(G3:G52))

Sumproduct & Count formula
 

Try this:

=SUMPRODUCT(--(ColA<7),--($D$3:$D52="y"),$G$3:$G52)

Note that empty cells in ColA will evaluate to 0 and 0 is less than 7. So,
empty cells could lead to incorrect results. If you need to account for
empty cells:

=SUMPRODUCT(--(ColA<""),--(ColA<7),--($D$3:$D52="y"),$G$3:$G52)


--
Biff
Microsoft Excel MVP


"VickiMc" wrote in message
...
That one works equally as well as Petes, though I must admit it looks a
lot
tidier than my original.

For my next problem - whilst I have your attention -
my formula is
=SUMPRODUCT((ColA<7)*($D$3:$D52="y")*(COUNT($G$3:$ G52)))
What I want it to do is only sum the cells in Col G if it meets the
criteria
of being less than 7 in Column A, and Column D contains a Y.
What it is doing is evaluating to a 1 for ColA, a 1 for ColD and 23 (being
the total count of cells for Column G) for a total of 46. (1+1*23=46).
What it should be giving me is 20, there are 2 items that fit the Criteria
of <7, and "Y", and each have a figure of 10 in Column G.

"joeu2004" wrote:

On Dec 4, 4:27 pm, VickiMc wrote:
Here is my formula
=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(COUNT(G3 :G52)))
Column G contains either text or numbers.
What I want to achieve is for the formula to count how many
cells in column G contain a number if Column A contains a
3, a 4 or a 5.

Not sure whether ColA is a named range or a single cell. Just to be
clear, does one of the following work for you?

=sumproduct((A1={3,4,5})*isnumber(G3:G52))

=sumproduct((A3:A52={3,4,5})*isnumber(G3:G52))

Sumproduct & Count formula
 

The first of the two formuli worked, I'm assuming because it is a formula
that determines what number is placed in ColA, so essentially it isn't a
blank cell(?).
Aa a precautionary measure I've printed off this page, so that if in the
future something goes wrong, this may be the fix I need.
Cheers to You All
Fond Regards & Total Respect,
Vicki


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(ColA<7),--($D$3:$D52="y"),$G$3:$G52)

Note that empty cells in ColA will evaluate to 0 and 0 is less than 7. So,
empty cells could lead to incorrect results. If you need to account for
empty cells:

=SUMPRODUCT(--(ColA<""),--(ColA<7),--($D$3:$D52="y"),$G$3:$G52)


--
Biff
Microsoft Excel MVP


"VickiMc" wrote in message
...
That one works equally as well as Petes, though I must admit it looks a
lot
tidier than my original.

For my next problem - whilst I have your attention -
my formula is
=SUMPRODUCT((ColA<7)*($D$3:$D52="y")*(COUNT($G$3:$ G52)))
What I want it to do is only sum the cells in Col G if it meets the
criteria
of being less than 7 in Column A, and Column D contains a Y.
What it is doing is evaluating to a 1 for ColA, a 1 for ColD and 23 (being
the total count of cells for Column G) for a total of 46. (1+1*23=46).
What it should be giving me is 20, there are 2 items that fit the Criteria
of <7, and "Y", and each have a figure of 10 in Column G.

"joeu2004" wrote:

On Dec 4, 4:27 pm, VickiMc wrote:
Here is my formula
=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(COUNT(G3 :G52)))
Column G contains either text or numbers.
What I want to achieve is for the formula to count how many
cells in column G contain a number if Column A contains a
3, a 4 or a 5.

Not sure whether ColA is a named range or a single cell. Just to be
clear, does one of the following work for you?

=sumproduct((A1={3,4,5})*isnumber(G3:G52))

=sumproduct((A3:A52={3,4,5})*isnumber(G3:G52))

Sumproduct & Count formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"VickiMc" wrote in message
...
The first of the two formuli worked, I'm assuming because it is a formula
that determines what number is placed in ColA, so essentially it isn't a
blank cell(?).
Aa a precautionary measure I've printed off this page, so that if in the
future something goes wrong, this may be the fix I need.
Cheers to You All
Fond Regards & Total Respect,
Vicki


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(ColA<7),--($D$3:$D52="y"),$G$3:$G52)

Note that empty cells in ColA will evaluate to 0 and 0 is less than 7.
So,
empty cells could lead to incorrect results. If you need to account for
empty cells:

=SUMPRODUCT(--(ColA<""),--(ColA<7),--($D$3:$D52="y"),$G$3:$G52)


--
Biff
Microsoft Excel MVP


"VickiMc" wrote in message
...
That one works equally as well as Petes, though I must admit it looks a
lot
tidier than my original.

For my next problem - whilst I have your attention -
my formula is
=SUMPRODUCT((ColA<7)*($D$3:$D52="y")*(COUNT($G$3:$ G52)))
What I want it to do is only sum the cells in Col G if it meets the
criteria
of being less than 7 in Column A, and Column D contains a Y.
What it is doing is evaluating to a 1 for ColA, a 1 for ColD and 23
(being
the total count of cells for Column G) for a total of 46. (1+1*23=46).
What it should be giving me is 20, there are 2 items that fit the
Criteria
of <7, and "Y", and each have a figure of 10 in Column G.

"joeu2004" wrote:

On Dec 4, 4:27 pm, VickiMc wrote:
Here is my formula
=SUMPRODUCT(((ColA=3)+(ColA=4)+(ColA=5))*(COUNT(G3 :G52)))
Column G contains either text or numbers.
What I want to achieve is for the formula to count how many
cells in column G contain a number if Column A contains a
3, a 4 or a 5.

Not sure whether ColA is a named range or a single cell. Just to be
clear, does one of the following work for you?

=sumproduct((A1={3,4,5})*isnumber(G3:G52))

=sumproduct((A3:A52={3,4,5})*isnumber(G3:G52))