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

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

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

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

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