sumproduct

[jessicafang]

Hi everyone,

Need help from you guys.

TO avoid having #value, I have to use sumproduct formula, can I include <"01/01/2015" within the sumproduct formula as I need to sumif the column B under the condition of before 2015 and after 2015 without creating a new column.

Many thanks,
Jess

 

[husoi]

Hi everyone,

Need help from you guys.

TO avoid having #value, I have to use sumproduct formula, can I include <"01/01/2015" within the sumproduct formula as I need to sumif the column B under the condition of before 2015 and after 2015 without creating a new column.

Many thanks,
Jess

Hi Jessica,

I would use sumifs formula instead. but that will depend on which version of Excel you're using.

 

[jessicafang]

Thanks for you reply. I am using 2013 Excel. Would sumifs won't give me #value when I close the linked file?

 

[husoi]

Thanks for you reply. I am using 2013 Excel. Would sumifs won't give me #value when I close the linked file?

HA

The eternal problem of external links... (I just hate them )

The information from external links only updates if you tell Excel to do it. try to disable the automatic external links update and it will solve the problem.

Another way is to incorporate the sheet you have the information into the spreadsheet and hide it. (you can set up a "veryhidden" status on the code view and prevent other users to see it (unless of course they know how to look for it).

 

[MARZIOTULLIO]

Hi Maybe
=SUMPRODUCT(--(B2:B6>=B2),--(B2:B6<=B3),--(A2:A6)) 23=VALUE BETWEEN 1/1/2014 AND 4/23/2015


[TABLE="width: 112"]
<colgroup><col width="56" span="2" style="width:42pt"> </colgroup><tbody>[TR]
[TD="width: 56"]
[TABLE="width: 112"]
<colgroup><col width="56" span="2" style="width:42pt"> </colgroup><tbody>[TR]
[TD="width: 56"]VALUE [/TD]
[TD="width: 56"] DATE[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl65, align: right"]1/1/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl65, align: right"] 4/23/2015[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="class: xl65, align: right"]1/1/2016[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="class: xl65, align: right"]4/5/2014[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="class: xl65, align: right"]2/23/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 56"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Thanks for you reply. I am using 2013 Excel. Would sumifs won't give me #value when I close the linked file?

If you could post what your sumif(s) formula would look like if that wasn't an issue.
That would make it easier to convert it to sumproduct.

 

[jessicafang]

You are a STAR. It worked. I guess I can't use "01/01/2015", I had to select the cell for it to work.

 

[Jonmo1]

can I include <"01/01/2015" within the sumproduct formula

Within the sumproduct, you'd have to convert "01/01/2015" to a real date, sumproduct is seeing it as just a text string.

Try it like
<"01/01/2015"+0
Or even
< DATE(2015,1,1)
<date(2015,1,1)< html=""></date(2015,1,1)<>

 

[jessicafang]

Within the sumproduct, you'd have to convert "01/01/2015" to a real date, sumproduct is seeing it as just a text string.

Try it like
<"01/01/2015"+0
Or even
< DATE(2015,1,1)
<date(2015,1,1)< html=""></date(2015,1,1)<>

It didn't work, just tried both, not sure where I did was wrong though.

 

[Jonmo1]

Well, it's certainly possible to hard code dates into a formula.
To find out why it's not working for you, You'd have to post the sumproduct you tried that failed.
And it would help to see the sumif(s) that works when the book is open.