CODEWITHSUNDEEP
excelexcel-formula
aab
aab

Reputation: 1739

Excel formula to calculate values based on other cells

I've got the following table: enter image description here

I'm using the following formula in column F to get the sum of the values in column E: =SUMIFS(E:E,C:C,LEFT(TRIM(RIGHT([@[Object Name]],LEN([@[Object Name]])-FIND("=",[@[Object Name]]))),4),A:A,INT([@[Result Time]])) but it doesn't seem to work.

Evaluating the formula gives me the following result: enter image description here

Can someone please take a took at the for

Upvotes: 1

Views: 1920

Answers (1)

Gravitate
Gravitate

Reputation: 3064

You are looking for an object name of "T054", when they all appear to be "CELL=T054CID"... so no results are being returned.

What exactly is it you are trying to do?

Is there any reason you cannot just do:

=SUMIFS([3G_Traffic_CS],[Object Name],[@[Object Name]],[Result Time],[@[Result Time]])

enter image description here

You will also notice that I am referencing just the column cells within the table, rather than the entire column. This is more efficient and will prevent any extra values which appear under the table being counted (unlikely but not impossible).

ALTERNATIVE (Using a Helper Column)

Add a helper column which contains a trimmed value of the Object Name and compare to that:

=SUMIFS([3G_Traffic_CS],[Trimmed Object Name],[@[Trimmed Object Name]],[Result Time],[@[Result Time]])

enter image description here

ANOTHER ALTERNATIVE (Using FIND and SUMPRODUCT)

If you want to include any Object Name which CONTAINS your trimmed value, you can do this using the FINDfunction with SUMPRODUCT:

=SUMPRODUCT(
    ([Result Time]=[@[Result Time]])*
    (ISNUMBER(
        FIND(LEFT(TRIM(RIGHT([@[Object Name]],LEN([@[Object Name]])-FIND("=",[@[Object Name]]))),4),
            [Object Name]
        )
    ))*
    [3G_Traffic_CS]
)

enter image description here

YET ANOTHER ALTERNATIVE (Using a Wildcard as per @Jvdv's comment)

Again, this assumes that you want to include all Object Names which CONTAIN your trimmed site ID. Simply add a "*" to the start and end of your trimmed Object Name.

=SUMIFS([3G_Traffic_CS],
    [Object Name],
    CONCAT("*",
        LEFT(TRIM(RIGHT([@[Object Name]],LEN([@[Object Name]])-FIND("=",[@[Object Name]]))),4),
        "*"
    ),
    [Result Time],
    INT([@[Result Time]])
)

enter image description here

Upvotes: 3

Related Questions