Sumproduct, multiple conditions.. but I also want to "minus" somet

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

Guest

=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(--(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..?
Click to expand...

I think this will work for you...

=SUMPRODUCT(--(LEFT(Rawdata!H2:H851,4)="Janu")*NOT(ISNUMBER(Rawdata!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
 
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"
 
You're confusing everyone when you say:
if colum B37
now the b37 colum
Click to expand...

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
 
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
 
Back
Top