Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
I am trying to utilize a SUMIF function that references a cell to provide the
the criteria, so that I can changed the criteria inside a optimization or scenario. My functions are all returning zero when I use the cell reference, but when I use the actual value, it returns what it should? Any way I can get around this? |
#2
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
It should work properly if you are looking for an equal condition.
However, if you are looking for ,<,or <, then you need something like =SUMIF(CritRange,""&A1,SumRange) where your criterion value is in A1, if you are looking for an inequality. You put whatever inequality sign you need between the quotes. HTH DOR |
#3
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
On Sun, 13 Nov 2005 13:50:06 -0800, "Albie"
wrote: I am trying to utilize a SUMIF function that references a cell to provide the the criteria, so that I can changed the criteria inside a optimization or scenario. My functions are all returning zero when I use the cell reference, but when I use the actual value, it returns what it should? Any way I can get around this? It's just a syntax issue: =SUMIF(rng_to_check, "=" & cell_ref, rng_to_sum) --ron |
#4
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
Thanks! Works wonders!
"Ron Rosenfeld" wrote: On Sun, 13 Nov 2005 13:50:06 -0800, "Albie" wrote: I am trying to utilize a SUMIF function that references a cell to provide the the criteria, so that I can changed the criteria inside a optimization or scenario. My functions are all returning zero when I use the cell reference, but when I use the actual value, it returns what it should? Any way I can get around this? It's just a syntax issue: =SUMIF(rng_to_check, "=" & cell_ref, rng_to_sum) --ron |
#5
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
Thanks! Works great. I was suspecting syntax but as usual, the help files
didn't help "DOR" wrote: It should work properly if you are looking for an equal condition. However, if you are looking for ,<,or <, then you need something like =SUMIF(CritRange,""&A1,SumRange) where your criterion value is in A1, if you are looking for an inequality. You put whatever inequality sign you need between the quotes. HTH DOR |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
I am trying to use a countif formula to count column a - rows 6-99 if
between10%and 25% AND if column b =DS. can you help - i have tried the sumproduct - might not have the right formula to put in but I tried and i have tried the function and several other suggestions the help provided. I would greatly appreciate it. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
Try this:
=SUMPRODUCT(--(A6:A99=10%),--(A6:A99<=25%),--(B6:B99="DS")) -- Biff Microsoft Excel MVP "Laura" wrote in message ... I am trying to use a countif formula to count column a - rows 6-99 if between10%and 25% AND if column b =DS. can you help - i have tried the sumproduct - might not have the right formula to put in but I tried and i have tried the function and several other suggestions the help provided. I would greatly appreciate it. Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
it works!!! Thank you so very much.
"T. Valko" wrote: Try this: =SUMPRODUCT(--(A6:A99=10%),--(A6:A99<=25%),--(B6:B99="DS")) -- Biff Microsoft Excel MVP "Laura" wrote in message ... I am trying to use a countif formula to count column a - rows 6-99 if between10%and 25% AND if column b =DS. can you help - i have tried the sumproduct - might not have the right formula to put in but I tried and i have tried the function and several other suggestions the help provided. I would greatly appreciate it. Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Laura" wrote in message ... it works!!! Thank you so very much. "T. Valko" wrote: Try this: =SUMPRODUCT(--(A6:A99=10%),--(A6:A99<=25%),--(B6:B99="DS")) -- Biff Microsoft Excel MVP "Laura" wrote in message ... I am trying to use a countif formula to count column a - rows 6-99 if between10%and 25% AND if column b =DS. can you help - i have tried the sumproduct - might not have the right formula to put in but I tried and i have tried the function and several other suggestions the help provided. I would greatly appreciate it. Thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
Hi Im having a similar issue, I'm trying to count numbers within a range and then substract that number to another countif formula so something like this: =COUNTIF('Data Report'!$N$14:$N$2857,"O-PFS"-(COUNTIF('Data Report'!$O$14:$O$2857,"75<85"))) so N coulmn has codes like"o-pfs" and O columns has numbers, in a separate sheet I need to count the number of N with "o-pfs" minus O column within 75 and 85.. does this make sense? my formula gives me 0 in return??? thanks |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
=COUNTIF('Data Report'!$N$14:$N$2857,"O-PFS"-
(COUNTIF('Data Report'!$O$14:$O$2857,"75")-COUNTIF('Data Report'!$O$14:$O$2857,"=85")) -- HTH Bob "ginger82" wrote in message ... Hi Im having a similar issue, I'm trying to count numbers within a range and then substract that number to another countif formula so something like this: =COUNTIF('Data Report'!$N$14:$N$2857,"O-PFS"-(COUNTIF('Data Report'!$O$14:$O$2857,"75<85"))) so N coulmn has codes like"o-pfs" and O columns has numbers, in a separate sheet I need to count the number of N with "o-pfs" minus O column within 75 and 85.. does this make sense? my formula gives me 0 in return??? thanks |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a Reference inside a SUMIF or COUNTIF Function?
Hello,
A slightly different query for you.. I'm trying to use a countif on a particular "calendar", which shows days across the sheet and named individuals down the sheet. The main content shows what piece of work day by day each person is planned to be working on. All "task" values entered are selected from drop-down lists the data for which are sourced from a separate range in a second sheet (call it Jobs for argument's sake).. so for example: 10/05 11/05 12/05 13/05 name1 defects defects defects P2 Build name2 P1 Build CCR123 CCR123 P1 Build name3 CCR49 CCR49 P2 Build P2 Build We currently use some summary COUNTIF functions at the bottom of the first worksheet to sum up: - the number of people in a given day working on Project 1 build; - the number of people working on Project 2 build; - the number of people working on defects etc. But some of the work (e.g. change control requests (CCRs)) may relate to P1 build or P2 build. So what I want to do (rather than amending the formula throughout the year as new CCRs come in) is for each daily column, count up all entries that show "P1 Build" AND in the same formula count any CCRs which are categorised as "P1 Build" in the lookup table on the "Jobs" tab. The range on the second tab looks like this: Deliverable Workstream CCR123 P1 Build CCR456 P2 Build CCR789 P1 Build CCR889 etc. P2 Build defects defects etc. The formula I'm currently trying (which is failing to do the lookup) is as follows: =COUNTIF(CW7:CW76,"P2 Build") + COUNTIF(CW7:CW76,VLOOKUP(CW7:CW76,Jobs!A59:A122,2, FALSE)&"=P2 Build") So count all instances of "P2 Build" in the main sheet, range CW7:CW76. Add to this any entries from the main sheet for which the second column on the Jobs sheet is set to "P2 Build". Any suggestions appreciated... Thank you Glyn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the TODAY function with the SUMIF function? | Excel Worksheet Functions | |||
Could the "Criteria" in COUNTIF function be a cell reference? | Excel Worksheet Functions | |||
MIN Function w/ variable address reference | Excel Worksheet Functions | |||
Lookup function returning reference, not value | Excel Worksheet Functions | |||
SUMIF function | Excel Worksheet Functions |