0

I've been struggling with this one for a while... I have the following data table on a sheet called PickupData

enter image description here

I then have a sheet where i'm trying to sum income (col d) where org_code (col a) = the value in row 18 and month (col c) = the value in column a

enter image description here

I've tried with SUMPRODUCT, SUM(IF(blah,IF(blah,x,0),0) (pressing ctrl-shift-enter) and even the conditional sum wizard. I don't think i can use DSUM as it requires a cell range to be passed for the criteria parameter.

I also tried un-merging the cells as i thought this could be effecting the results.

Can anyone provide a working solution?

Heres the test workbook: here

Thanks

Lee

Improve this question

3 Answers 3

Reset to default
3

Sumproduct will work for you:

=SUMPRODUCT(D1:D8,--(A1:A8=OrgRef),--(C1:C8=MonthRef))

OrgRef and MonthRef are placeholders for the address of your filters.

In your example book the Org code is text while your "Org_Code" field where you have 26 entered is a number. They won't match. Either convert your org codes to numbers or preface 26 with a ' to mark it as a string.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

genius- thank you! how could you tell some of the numbers were stored as strings? i'd expect excel to highlight the cells like it normally does with numbers stored as strings. also- what is the purposed of the --?
The most obvious sign is Excel left justifies strings and right justifies values. This can be overridden but is a good first check. Glad it worked for you.
damn- didnt realise but i actually tested this in excel 2010 on the mac (neither of the other solutions i tried worked before in 2010 or 2003)- this one now tried on 2003 doesn't seem to work. if you manage to get it working could you upload my modified .xls pleae?
Small bit of progress- the range seems to not allow D:D style... has to be explicitly defined D1:D8 (just experimenting to see if header row can/musn't be included
there seem to be a few weird limitations... not allowed named ranges or "entire column" D:D style references... but D1:D10000 works aok
|
0

Have you tried:

SUMIF()
SUMIFS()

or, Excel Macros?

Code

Sub Test1()
    Dim rRange As Range
    Set rRange = Range("C:C")

    Dim iSum As Integer
    For Each rRange In rRange

        If (rRange.Offset(0, -2) = 18) Then
            iSum = iSum + rRange.Value
        End If

        If (IsEmpty(rRange.Offset(1, 0))) Then
            Exit For
        End If
    Next rRange

    Debug.Print iSum
End Sub
Improve this answer

2 Comments

SUMIFS came in, in Excel 2007 and SUMIF doesn't support multiple criteria.
i am starting to think i may need to write a user defined function. could you explain the one you pasted? for each non-empty cell in column return the sum of all column c values where column a = 18?
0

The SUM(IF(... should work, but yes you will need to un-merge your cells because array-formulas do not work in merged cells. The following entered into B19 should work (remembering to ctrl+shift+enter)

=SUM(IF(PickupData!$A$2:$A$8=B$18,IF(PickupData!$C$2:$C$8=$A19,PickupData!$D$2:$D$8,0),0))
Improve this answer

1 Comment

tried that but no joy- i've uploaded the workbook and linked in the question if you have time to check for a mistake- thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.