# SUMPRODUCT OR SUM(IF ?

#### evanslee

##### New Member
Ok, heres what i need to do...
Similar too...

=SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=B5),--('Sheet1'!\$K\$2:\$K\$3223<B6))

but, i want to
• SUM(Sheet2!E2:E3223)
but only where it matches
• ('Sheet1'!\$K\$2:\$K\$3223>=B5),--('Sheet1'!\$K\$2:\$K\$3223<B6)

Should I be using some kind of SUM(IF statement ?

Regards,
Lee

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### infuse

##### Board Regular
Is this what you want?
If not can you explain this part one more time? ('Sheet1'!\$K\$2:\$K\$3223 …..
Code:
``SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=B5),(Sheet2!E2:E3223))``

Infuse

#### barry houdini

##### MrExcel MVP
Ok, heres what i need to do...
Similar too...

=SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=B5),--('Sheet1'!\$K\$2:\$K\$3223< B6))

but, i want to
• SUM(Sheet2!E2:E3223)
but only where it matches
• ('Sheet1'!\$K\$2:\$K\$3223>=B5),--('Sheet1'!\$K\$2:\$K\$3223< B6)

Should I be using some kind of SUM(IF statement ?

Regards,
Lee

Try this

=SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=B5),--('Sheet1'!\$K\$2:\$K\$3223< B6),'Sheet2'!E2:E3223)

#### evanslee

##### New Member
No, I need to look 3 colums that vary in length,

#ABC
1128
2124
3114
4126

I.e I want to sum(C1:C4) but only where it meets the criteria A1:A4=1 , and B1:B4=2

Im not sure if that makes it any easier, but SUMPRODUCT alone does not work, that does not SUM the amounts for me in column C (as in my ickle table example)

Can i do SUMPRODUCT(SUM(IF
but i only want one column to be summed if it meets the other two criteria in the array.

Regards,
Lee

#### barry houdini

##### MrExcel MVP

Lee,

Did you try my suggestion. It sums sheet 2 column E where your other two criteria are met? Isn't that what you want?

infuse

Mr Excel Board doesn't like < and > signs. In some circumstances they are interpreted at HTML tags and you lose the text. You can see what was posted if you quote it.

To avoid the problem put spaces before and after < and > signs or use the code tags, e.g.

Code:
``=(A1>B1)*(B1<C1)``

#### evanslee

##### New Member
I get an error, #VALUE!

Lee,

Did you try my suggestion. It sums sheet 2 column E where your other two criteria are met? Isn't that what you want?

infuse

Mr Excel Board doesn't like < and > signs. In some circumstances they are interpreted at HTML tags and you lose the text. You can see what was posted if you quote it.

To avoid the problem put spaces before and after < and > signs or use the code tags, e.g.

Code:
``=(A1>B1)*(B1<C1)``

#### Brian from Maui

##### MrExcel MVP

Care to post the formula you are using?

#### barry houdini

##### MrExcel MVP
You said that the columns vary in length, you need to use the same length ranges for sumproduct to work.

Assuming you do that then another cause of errors would be errors within your ranges.

If neither of these is the culprit then post the excat formula you're using

#### evanslee

##### New Member
Im using this formula entered as an array
Code:
``=SUMPRODUCT(--(Sheet1!\$A\$1:\$A\$10="n"),--(Sheet1!\$B\$1:\$B\$10="y"),Sheet1!C1:C10)``

but the value is showing as 0

#### evanslee

##### New Member
Code:
``=SUMPRODUCT(SUM(IF(--(Sheet1!\$A\$1:\$A\$10="n"),--(Sheet1!\$B\$1:\$B\$10="y"),Sheet1!C1:C10)))``

arghhhhh, I had the reference wrong, what a stupid mistake. sorry to all, you had it correct all along ! thanks so much, I have a red face now.

Thanks again

Lee

Replies
3
Views
124
Replies
2
Views
79
Replies
1
Views
55
Replies
5
Views
85
Replies
3
Views
102