| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count if two columns match different criteria | Excel Worksheet Functions | |||
| Count items when specific text and date criteria are met | Excel Worksheet Functions | |||
| Count Multiple Criteria | Excel Worksheet Functions | |||
| COUNT or SUM with multiple criteria | Excel Worksheet Functions | |||
| COUNT with Multiple Criteria Error | Excel Worksheet Functions | |||
Linear Mode
