Sumproduct & Count formula

[VickiMc]

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!

 

[Pete_UK]

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

 

[joeu2004]

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))

 

[VickiMc]

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

 

[Pete_UK]

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

Pete

 

[VickiMc]

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.

 

[T. Valko]

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)

 

[VickiMc]

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]

You're welcome. Thanks for the feedback!