# Advice- make sense of this...!

#### collinsc

##### Board Regular
Can someone help me make sense of the following....

=1-(SUMIF('Project Resource Plan'!\$A\$3:\$A\$124,\$B27,'Project Resource Plan'!AW\$3:AW\$124)+SUMIF(Completed!\$A\$2:\$A\$373,\$B27,Completed!AW\$2:AW\$373))

I assume the "=1" means if the code is correct then fill "1" in the cell?

what is the dash (-) after the 1 ?

'Project Resource Plan' and 'completed' are other sheets in the workbook.

What does the \$ mean?

what is the semicolon represent.?

There are 2 "IFs" do both have to be completed for a "1" to be inserted into the cell?

Thanks

Chris

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hello,

the dash after the 1 is subtracting the value after the 1 from 1 e.g. 1-8

the \$ signs make cell ranges absolute

ie.
if you have A1 as a ref then copy it to A2 it will change to A2. If you copy it to B1 it will change to B1

if you have A\$1 as a ref then copy it to A2 it will stay A1. If you copy it to B1 it will change to B\$1

if you have \$A1 as a ref then copy it to A2 it will change to \$A2. If you copy it to B1 it will stay \$A1

if you have \$A\$1 as a ref then copy it to A2 it will stay \$A\$1. If you copy it to B1 it will stay \$A\$1

The : indicates a range of cells e.g.

A1:A10 or A1:C1 or A1:F10

As for the two IF's it takes each SUMIF separately. so if one is true, but the other is false it will sum the true part.

=1 (1 is the starting value)

- (substiute/take away)

Project Resource Plan (Named Range)

Completed (Named Range)

\$ a fixed cell reference (i.e. will stay static and not move)

: says between two data rages (i.e. the table used for the data)

Sumif and If's are different.

Hope this has helped a bit.

Maybe you should search for Sumif on google. As this shold tell you a lot and give you walk throughs.

Richard

Hi,

The dash is a minus symbol. The formula is conditionally summing values in the ranges designated then subtracting the figure from 1.

Any help?

Sam

=1 means what it says. The - is a minus sign, so the remainder of the formula is deducted from 1. The colon is the range operator.

SUMIF is a function that takes 3 arguments - the range to evaluate, the criteria, and the range to sum. In:

SUMIF('Project Resource Plan'!\$A\$3:\$A\$124,\$B27,'Project Resource Plan'!AW\$3:AW\$124)

The range to evaluate is:

'Project Resource Plan'!\$A\$3:\$A\$124

The criteria is:

\$B27

and the range to sum is:

'Project Resource Plan'!AW\$3:AW\$124)

Each cell in the range to evaluate is compared with the criteria and if it matches the correspoding cell in the range to sum is included in the sum.

The \$ signs are used to fix the column or row reference when the formula is copied elsewhere.

http://office.microsoft.com/en-us/assistance/HP052255841033.aspx

Wow, thanks for all the responses. Its been a great help.

Replies
2
Views
298
Replies
4
Views
383
Replies
3
Views
474
Replies
7
Views
254
Replies
11
Views
640

1,219,770
Messages
6,150,166
Members
450,937
Latest member
kattyg261

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back