Count across multiple columns, using specific criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try:

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

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

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