SUMIFS using an OR operator

[miconian]

Basically, I'm using SUMIFS, and the final criterion could go one of two ways: either EMER or APPC are valid values.

Here's my formula:

=SUMIFS(Data2011[Net Amount],Data2011[Internet Site],A13,Data2011[Confidence %],"B",Data2011[Ad Unit],"EMER")

The last criterion, Data2011[Ad Unit],"EMER" is correct, but incomplete. Another valid criteria for [Ad Unit] should be "APPC".

So I'm trying to do something like this:

=SUMIFS(Data2011[Net Amount],Data2011[Internet Site],A13,Data2011[Confidence %],"B",Data2011[Ad Unit],"EMER" OR Data2011[Ad Unit],"APPC")

or perhaps:

SUMIFS(Data2011[Net Amount],Data2011[Internet Site],A13,Data2011[Confidence %],"B",OR((Data2011[Ad Unit],"EMER"),(Data2011[Ad Unit],"APPC"))

What am I missing?

 

[WouterP]

In E2 enter:

=DATE(YEAR(TODAY()),1,1)

In F2 enter:

=DATE(YEAR(TODAY()),8,1)

In G2 enter:

=SUMPRODUCT(C2:C5,--ISNUMBER(MATCH(A2:A5,{"CNN","NBC"},0)),
  --ISNUMBER(MATCH(B2:B5,{"red","blue","green"},0)),
  --($D$2:$D$5>=E2),--($D$2:$D$5<=F2))

This one triggered a click in my head. Think I get it on a more thorough level now. Thanks a lot!

 

[Aladin Akyurek]

This one triggered a click in my head. Think I get it on a more thorough level now. Thanks a lot!

You are welcome.

 

[WouterP]

I'm stretching my luck here - another question.

As you may expect I've got a few array functions running now. They're quite low in number, but high in memory usage.

When I open the workbook, all is well. When I click "calculate worksheet', it simply does. Yet when I do that a second time (after changing some inputs), excel gives the classic message that it ran out of resources, which caused it to leave some cells uncalculated.

I see that calculating the second time triggers a jump in RAM-usage. I would expect that RAM to 'get released' after the calculation, but I don't see that happening. Closing or saving the workbook doesn't do the trick either.

Yet when I close the application as a whole, then the RAM gets released. After that, I can again perform *one* calculate. Given I need to do about 40 (which I will put in a VBA-loop), that's not really an option.

Have you got any idea on how I can get excel to release that memory in order for it to be used by a subsequent calculate command?

I'd be ever so thankfull!

 

[WouterP]

Here an image of the Task Manager after the first calculate. As you see, somethings gets 'stuck' in there.

https://www.dropbox.com/s/3o7wstm1rvp5izw/RAM-problem.PNG?dl=0

 

[WouterP]

https://www.dropbox.com/s/75mml5ymk5ogts8/RAM-problem2.PNG?dl=0

 

[Aladin Akyurek]

I'm stretching my luck here - another question.

As you may expect I've got a few array functions running now. They're quite low in number, but high in memory usage.

When I open the workbook, all is well. When I click "calculate worksheet', it simply does. Yet when I do that a second time (after changing some inputs), excel gives the classic message that it ran out of resources, which caused it to leave some cells uncalculated.

I see that calculating the second time triggers a jump in RAM-usage. I would expect that RAM to 'get released' after the calculation, but I don't see that happening. Closing or saving the workbook doesn't do the trick either.

Yet when I close the application as a whole, then the RAM gets released. After that, I can again perform *one* calculate. Given I need to do about 40 (which I will put in a VBA-loop), that's not really an option.

Have you got any idea on how I can get excel to release that memory in order for it to be used by a subsequent calculate command?

I'd be ever so thankfull!

Did you implement the formulas such that they refer to whole columns? If so, care to post them?

 

[WouterP]

Yes, yes I did. I started altering some to (for example) A1:A65000 in stead of A:A, but 7 formula's further I'm getting the impression that it won't do the trick.

Formula using the most resources is this one I presume:

=SUMPRODUCT('4.1 Data'!$L:$L,--ISNA(MATCH('4.1 Data'!$A:$A,{"No_Hearing_Loss"},0)),
--ISNA(MATCH('4.1 Data'!$S:$S,{"Herstelling"},0)),
--('4.1 Data'!$E:$E>=H$13),
--('4.1 Data'!$E:$E<=H$14),
--('4.1 Data'!$D:$D=$D$2))

 

[Aladin Akyurek]

Yes, yes I did. I started altering some to (for example) A1:A65000 in stead of A:A, but 7 formula's further I'm getting the impression that it won't do the trick.

Formula using the most resources is this one I presume:

=SUMPRODUCT('4.1 Data'!$L:$L,--ISNA(MATCH('4.1 Data'!$A:$A,{"No_Hearing_Loss"},0)),
--ISNA(MATCH('4.1 Data'!$S:$S,{"Herstelling"},0)),
--('4.1 Data'!$E:$E>=H$13),
--('4.1 Data'!$E:$E<=H$14),
--('4.1 Data'!$D:$D=$D$2))

Let's check the following...

Define Lrow in Formulas | Name Manager as referring to:

=MATCH(9.99999999999999E+307,'4.1 Data'!$L:$L)

What follows assumes that the data starts in row 2 (adjust to suit)...

Define Arange as referring to:

='4.1 Data'!$A$2:INDEX('4.1 Data'!$A:$A,Lrow)

Define Drange as referring to:

='4.1 Data'!$D$2:INDEX('4.1 Data'!$D:$D,Lrow)

Define Erange as referring to:

='4.1 Data'!$E$2:INDEX('4.1 Data'!$E:$E,Lrow)

Define Lrange as referring to:

='4.1 Data'!$L$2:INDEX('4.1 Data'!$L:$L,Lrow)

Define Srange as referring to:

 ='4.1 Data'!$S$2:INDEX('4.1 Data'!$S:$S,Lrow)

The formula now becomes...

=SUMPRODUCT(Lrange,--ISNA(MATCH(Arange,{"No_Hearing_Loss"},0)),
  --ISNA(MATCH(Srange,{"Herstelling"},0)),
  --(Erange>=H$13),
  --(Erange<=H$14),
  --(Drange=$D$2))

 

[WouterP]

Functions in names, names in names, things I didn't know were possible.
This indeed should limit the memory I use to the necesarry bit in stead of, well, millions of lines. In abstract terms this is what I wanted to do, limit the ranges, yet I needed your tip to get there. Thanks.

I'm just finished putting in all the names (I had 45). Tomorrow I'll be putting them into the formula's to see whether they work. I expect them to work, though. I'll keep you posted.

 

[WouterP]

Seems likes it's doing the trick.


I've now just changed a few formula's: 12 of the 372 I have to do, but I forgot to slide them on into the full 372. Funny thing is, it already worked like a charm I think. Given that I monitored my RAM and it *dropped* in comparison to the starting point. I don't what kind of magic that is, but I don't rule out a coincidence there.


I'm gonna slide them on, try further. (I'm way too enthousiastic about this by the way, I feel as if I'm 12yrs old.)


Ok, pulled the formula's through. It is working beautifully. Thanks.

And then a few hundred thankses on top of that.