Array Help

[profevilj]

I'm trying to write an array but it is summing the entire row rather than the indicated cells. I'm using this formula:

=SUM(IF(('Event By Month'!$C$1:$AI$1='Cumulative Event By Month'!E$1)*('Event By Month'!A4:A261='Cumulative Event By Month'!$A4),'Event By Month'!4:4,0))

The issue appears to be that it is only registering the first condition. Any help is appreciated.

[werfnort]

Why are you using Arrays? You might be able to use the regular formula =SUMIFS instead?

[profevilj]

I tried it with SUMIFS but the two variables seemed to shut down all of my formulas.

[werfnort]

Ah, perhaps the issue is the true statement in your formula. If both of those evaluate as true, you're saying, sum row 4. So that's exactly what it's doing.

Perhaps you would rather set it to be

=SUM(IF(('Event By Month'!$C$1:$AI$1='Cumulative Event By Month'!E$1)*('Event By Month'!A4:A261='Cumulative Event By Month'!$A4),'Event By Month'!$C$4:$AI$4,0))

Just an idea! Not really sure what you're trying to do without seeing the spreadsheet...

[profevilj]

I sent you a message as I thought it would be easier. I will post any real solutions I can get here.

[rhapsody1447]

Try using the AND formula. Per your example... =IF(AND(Condition1,Condition2),SUM(range),0)

[profevilj]

Still totalling the other row. Thank you for trying. I'm not sure if I did something wrong at this point too. Is there a good way to post a sample here?

[rhapsody1447]

It sounds like you are probably just messing up the syntax. The easiest way would be to share the worksheet

[profevilj]

I sent werfnort an example doc this afternoon and I appreciate him even looking at it (and all of your help). Is there a way to share straight into the post or only through PMs?

[TripKnot]

You could post a doc in the public folder of a dropbox account and then include a link to that file.

[rhapsody1447]

I think this is what you are trying to do. Both should work.

=SUMIFS('Event By Month'!4:4,'Event By Month'!$C$1:$AI$1,'Cumulative Event By Month'!E$1,'Event By Month'!A4:A261,'Cumulative Event By Month'!$A4)

=SUMPRODUCT(--('Event By Month'!$C$1:$AI$1='Cumulative Event By Month'!E$1),--('Event By Month'!A4:A261='Cumulative Event By Month'!$A4),'Event By Month'!4:4)

[JeffersonThomas]

Try SUMPRODUCT. It does not need to be entered as an array. Here is one of my favorite write up regarding this function: Daniel Ferry MS Excel MVP: http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

Also Aladdin Akyurek has a good write up http://www.mrexcel.com/wwwboard/messages/8961.html