Count across multiple columns, using specific criteria
 

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as greater than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together, it is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," 0"))

Can anyone tell me where I'm going wrong

Count across multiple columns, using specific criteria
 

Try:

=SUMPRODUCT(--(G$3:G$370),--($E$3:$E$37=$F68))

The '--' converts TRUE/FALSE to 1/0

HTH

"MMcQ" wrote:

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as greater than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together, it is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," 0"))

Can anyone tell me where I'm going wrong

Count across multiple columns, using specific criteria
 

Excellent, this has worked perfectly, thanks a million. I have played with
this for the best part of a day trying to get an answer

"Toppers" wrote:

Try:

=SUMPRODUCT(--(G$3:G$370),--($E$3:$E$37=$F68))

The '--' converts TRUE/FALSE to 1/0

HTH

"MMcQ" wrote:

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as greater than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together, it is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," 0"))

Can anyone tell me where I'm going wrong

Count across multiple columns, using specific criteria
 

Ask earlier next time!!! Thanks for the feedback.

"MMcQ" wrote:

Excellent, this has worked perfectly, thanks a million. I have played with
this for the best part of a day trying to get an answer

"Toppers" wrote:

Try:

=SUMPRODUCT(--(G$3:G$370),--($E$3:$E$37=$F68))

The '--' converts TRUE/FALSE to 1/0

HTH

"MMcQ" wrote:

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as greater than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together, it is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," 0"))

Can anyone tell me where I'm going wrong

Count across multiple columns, using specific criteria
 

You will be sorry you offered!!

I'm working on same spreadsheet and thought the same forumla would work if
checking text in two fields, i.e. if it equals Gap in one and SME in another,
or variables as I define.

Would you take a quick look at this formula and see where I'm going wrong -
sorry!

=SUMPRODUCT(--('Project Master'!G82:G369=Gap),--('Project
Master'!F82:F369=SME))

"Toppers" wrote:

Ask earlier next time!!! Thanks for the feedback.

"MMcQ" wrote:

Excellent, this has worked perfectly, thanks a million. I have played with
this for the best part of a day trying to get an answer

"Toppers" wrote:

Try:

=SUMPRODUCT(--(G$3:G$370),--($E$3:$E$37=$F68))

The '--' converts TRUE/FALSE to 1/0

HTH

"MMcQ" wrote:

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as greater than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together, it is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," 0"))

Can anyone tell me where I'm going wrong

Count across multiple columns, using specific criteria
 

I got the answer, I had to use
=SUMPRODUCT(--('Project Master'!G$82:G$369="Gap"),
--('Project Master'!$F$82:$F$369=F57))

I thought I had tried it with the "" but obviously didn't....


Maggie

"MMcQ" wrote:

You will be sorry you offered!!

I'm working on same spreadsheet and thought the same forumla would work if
checking text in two fields, i.e. if it equals Gap in one and SME in another,
or variables as I define.

Would you take a quick look at this formula and see where I'm going wrong -
sorry!

=SUMPRODUCT(--('Project Master'!G82:G369=Gap),--('Project
Master'!F82:F369=SME))

"Toppers" wrote:

Ask earlier next time!!! Thanks for the feedback.

"MMcQ" wrote:

Excellent, this has worked perfectly, thanks a million. I have played with
this for the best part of a day trying to get an answer

"Toppers" wrote:

Try:

=SUMPRODUCT(--(G$3:G$370),--($E$3:$E$37=$F68))

The '--' converts TRUE/FALSE to 1/0

HTH

"MMcQ" wrote:

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as greater than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together, it is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," 0"))

Can anyone tell me where I'm going wrong

Count across multiple columns, using specific criteria
 

Yippee - This one was driving me mad so went scrolling and found this answer
- Fabulous thanks, exactly what I was after - works a treat. One further
Question I now have (as can use this elsewhere) I have written the following:
=SUMPRODUCT(--('Q3'!$C$2:$C$117="Stage"),--('Q3'!$H$2:$H$117="**-Hazard"))

I have the **- as wildcards - this is because in that column there are
various areas listed with Hazard following an area ie WG-Hazard - I want to
ignore the areas and just count Hazard

Hope this makes sense

--
Thanks as always


"Toppers" wrote:

Try:

=SUMPRODUCT(--(G$3:G$370),--($E$3:$E$37=$F68))

The '--' converts TRUE/FALSE to 1/0

HTH

"MMcQ" wrote:

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as greater than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together, it is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," 0"))

Can anyone tell me where I'm going wrong

Count across multiple columns, using specific criteria
 

=SUMPRODUCT(--('Q3'!$C$2:$C$117="Stage"),--('Q3'!$H$2:$H$117="**-Hazard"))

Try this:

=SUMPRODUCT(--('Q3'!$C$2:$C$117="Stage"),--(ISNUMBER(SEARCH("hazard",'Q3'!$H$2:$H$117))))

--
Biff
Microsoft Excel MVP


"Lise" wrote in message
...
Yippee - This one was driving me mad so went scrolling and found this
answer
- Fabulous thanks, exactly what I was after - works a treat. One further
Question I now have (as can use this elsewhere) I have written the
following:
=SUMPRODUCT(--('Q3'!$C$2:$C$117="Stage"),--('Q3'!$H$2:$H$117="**-Hazard"))

I have the **- as wildcards - this is because in that column there are
various areas listed with Hazard following an area ie WG-Hazard - I want
to
ignore the areas and just count Hazard

Hope this makes sense

--
Thanks as always


"Toppers" wrote:

Try:

=SUMPRODUCT(--(G$3:G$370),--($E$3:$E$37=$F68))

The '--' converts TRUE/FALSE to 1/0

HTH

"MMcQ" wrote:

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as greater
than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together, it
is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," 0"))

Can anyone tell me where I'm going wrong

Count across multiple columns, using specific criteria
 

Wonderful Biff - thanks so much :-)
--
Thanks

Lise


"T. Valko" wrote:

=SUMPRODUCT(--('Q3'!$C$2:$C$117="Stage"),--('Q3'!$H$2:$H$117="**-Hazard"))

Try this:

=SUMPRODUCT(--('Q3'!$C$2:$C$117="Stage"),--(ISNUMBER(SEARCH("hazard",'Q3'!$H$2:$H$117))))

--
Biff
Microsoft Excel MVP


"Lise" wrote in message
...
Yippee - This one was driving me mad so went scrolling and found this
answer
- Fabulous thanks, exactly what I was after - works a treat. One further
Question I now have (as can use this elsewhere) I have written the
following:
=SUMPRODUCT(--('Q3'!$C$2:$C$117="Stage"),--('Q3'!$H$2:$H$117="**-Hazard"))

I have the **- as wildcards - this is because in that column there are
various areas listed with Hazard following an area ie WG-Hazard - I want
to
ignore the areas and just count Hazard

Hope this makes sense

--
Thanks as always


"Toppers" wrote:

Try:

=SUMPRODUCT(--(G$3:G$370),--($E$3:$E$37=$F68))

The '--' converts TRUE/FALSE to 1/0

HTH

"MMcQ" wrote:

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as greater
than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together, it
is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," 0"))

Can anyone tell me where I'm going wrong

Count across multiple columns, using specific criteria
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lise" wrote in message
...
Wonderful Biff - thanks so much :-)
--
Thanks

Lise


"T. Valko" wrote:

=SUMPRODUCT(--('Q3'!$C$2:$C$117="Stage"),--('Q3'!$H$2:$H$117="**-Hazard"))

Try this:

=SUMPRODUCT(--('Q3'!$C$2:$C$117="Stage"),--(ISNUMBER(SEARCH("hazard",'Q3'!$H$2:$H$117))))

--
Biff
Microsoft Excel MVP


"Lise" wrote in message
...
Yippee - This one was driving me mad so went scrolling and found this
answer
- Fabulous thanks, exactly what I was after - works a treat. One
further
Question I now have (as can use this elsewhere) I have written the
following:
=SUMPRODUCT(--('Q3'!$C$2:$C$117="Stage"),--('Q3'!$H$2:$H$117="**-Hazard"))

I have the **- as wildcards - this is because in that column there are
various areas listed with Hazard following an area ie WG-Hazard - I
want
to
ignore the areas and just count Hazard

Hope this makes sense

--
Thanks as always


"Toppers" wrote:

Try:

=SUMPRODUCT(--(G$3:G$370),--($E$3:$E$37=$F68))

The '--' converts TRUE/FALSE to 1/0

HTH

"MMcQ" wrote:

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as
greater
than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together,
it
is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," 0"))

Can anyone tell me where I'm going wrong