Count across multiple columns, using specific criteria

M

MMcQ

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
 
T

Toppers

Try:

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

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

HTH
 
M

MMcQ

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
 
M

MMcQ

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

MMcQ

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
 
L

Lise

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
 
T

T. Valko

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