Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct, multiple conditions.. but I also want to "minus" somet
=SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu"),--(Rawdata!J2:J851=""))
The above is my current formula.. What that is doing is asking the rawdata sheet to count how many assets are due for refresh in january, but have a blank location field.. What I want it to do now is to ALSO check to see if colum B37 (completed refresh's) has a number in it, and if it does, remove it from the total number that the formula originally gets.. So if there are 25 assets due for refersh in january without location dates, but I've marked 5 of those assets as been completed (which is what the b37 colum reports), then remove that number from the 25 to give me the total of 20..? Much appriciated!.. I bit buggered on this one. Regards, Jeffrey |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct, multiple conditions.. but I also want to "minus" somet
=SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu"),--(Rawdata!J2:J851=""))
The above is my current formula.. What that is doing is asking the rawdata sheet to count how many assets are due for refresh in january, but have a blank location field.. What I want it to do now is to ALSO check to see if colum B37 (completed refresh's) has a number in it, and if it does, remove it from the total number that the formula originally gets.. So if there are 25 assets due for refersh in january without location dates, but I've marked 5 of those assets as been completed (which is what the b37 colum reports), then remove that number from the 25 to give me the total of 20..? I think this will work for you... =SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu")*NOT(ISNUMBER(Rawd ata!B2:B851)),--(Rawdata!J2:J851="")) where I assumed that when you said column B37, you meant column B across the same range of rows as the other expressions used. Rick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct, multiple conditions.. but I also want to "minus" s
mmm I don't think so..
The B colum is just a number... Colum B has a formula that gets the number of "completed" assets from the rawdata tab.. so B is ONLY a number colum, it currently has 0 and today I set 2 items to completed in the rawdata tab, so now the b37 colum in the summary tab has 2 completed items. I want the below to be able to go "Ok, so I have 25 assets for refresh, but b37 says that 2 are completed so I only have 23" "Rick Rothstein (MVP - VB)" wrote: =SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu"),--(Rawdata!J2:J851="")) The above is my current formula.. What that is doing is asking the rawdata sheet to count how many assets are due for refresh in january, but have a blank location field.. What I want it to do now is to ALSO check to see if colum B37 (completed refresh's) has a number in it, and if it does, remove it from the total number that the formula originally gets.. So if there are 25 assets due for refersh in january without location dates, but I've marked 5 of those assets as been completed (which is what the b37 colum reports), then remove that number from the 25 to give me the total of 20..? I think this will work for you... =SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu")*NOT(ISNUMBER(Rawd ata!B2:B851)),--(Rawdata!J2:J851="")) where I assumed that when you said column B37, you meant column B across the same range of rows as the other expressions used. Rick |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct, multiple conditions.. but I also want to "minus" s
You're confusing everyone when you say:
if colum B37 now the b37 colum B37 isn't a column, it's a cell. So, the only cell in column B that you're interested in is cell B37, right? If that's the case just subtract B37 from your formula: =SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu"),--(Rawdata!J2:J851=""))-B37 Biff "Jeffa" wrote in message ... mmm I don't think so.. The B colum is just a number... Colum B has a formula that gets the number of "completed" assets from the rawdata tab.. so B is ONLY a number colum, it currently has 0 and today I set 2 items to completed in the rawdata tab, so now the b37 colum in the summary tab has 2 completed items. I want the below to be able to go "Ok, so I have 25 assets for refresh, but b37 says that 2 are completed so I only have 23" "Rick Rothstein (MVP - VB)" wrote: =SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu"),--(Rawdata!J2:J851="")) The above is my current formula.. What that is doing is asking the rawdata sheet to count how many assets are due for refresh in january, but have a blank location field.. What I want it to do now is to ALSO check to see if colum B37 (completed refresh's) has a number in it, and if it does, remove it from the total number that the formula originally gets.. So if there are 25 assets due for refersh in january without location dates, but I've marked 5 of those assets as been completed (which is what the b37 colum reports), then remove that number from the 25 to give me the total of 20..? I think this will work for you... =SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu")*NOT(ISNUMBER(Rawd ata!B2:B851)),--(Rawdata!J2:J851="")) where I assumed that when you said column B37, you meant column B across the same range of rows as the other expressions used. Rick |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct, multiple conditions.. but I also want to "minus" somet
Hi Jeffrey
Assuming your column H has Text values in it, and not Excel dates, then all you need do is subtract B37 from the result =SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu"),--(Rawdata!J2:J851=""))-Rawdata!$B$37 -- Regards Roger Govier "Jeffa" wrote in message ... =SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu"),--(Rawdata!J2:J851="")) The above is my current formula.. What that is doing is asking the rawdata sheet to count how many assets are due for refresh in january, but have a blank location field.. What I want it to do now is to ALSO check to see if colum B37 (completed refresh's) has a number in it, and if it does, remove it from the total number that the formula originally gets.. So if there are 25 assets due for refersh in january without location dates, but I've marked 5 of those assets as been completed (which is what the b37 colum reports), then remove that number from the 25 to give me the total of 20..? Much appriciated!.. I bit buggered on this one. Regards, Jeffrey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))*(k5:k160="") | Excel Discussion (Misc queries) | |||
Syntax to "OR" 3 "ISERROR" conditions | Excel Worksheet Functions | |||
Sumproduct-multiple criteria for same range "OR" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Please, need help with multiple "if" conditions | Excel Discussion (Misc queries) |