sum range of numbers if two criterias are met.

[freshstart]

Hello all,

Basically,

I want to add a range of numbers in column AB

Cell range in AB

Year 1 = 04 to 364
Year 2 = 365 to 1035
Year 3 = 1036 to 1662
Year 4 = 1663 to 2500

if

'unemployed' is in column AD

and

'500' is in AX:BI

any ideas?

once again, much appreciated

[Domenic]

Somewhat unclear... Can you post a small sample of the data (about 5 rows), along with the expected result?

[freshstart]

Please find attached:

Outcome:
so there is 1 match of 'unemployed' and '500' with 120 months so the sum would be 120.

Anything else please do let me know.

thanks again.

[Domenic]

Try...

=SUMPRODUCT(--($B$4:$B$8="Unemployed"),--($J$4:$J$8=500),$A$4:$A$8)

Although, from your original post, it looks like you're probably looking for something like this...

=SUM(IF(MMULT(IF(B4:B8="Unemployed",IF(C3:N3="Committed",IF(C4:N8=500,1,0),0),0),TRANSPOSE(COLUMN(C3:N3)^0)),A4:A8))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

[freshstart]

Hello Again,

this formula

=SUMPRODUCT(--($B$4:$B$8="Unemployed"),--($J$4:$J$8=500),$A$4:$A$8)

This works witht the worksheet provided, however...

comes up with a value error when tweaked to suit the worksheet (Tracking System):

SUMPRODUCT('Tracking System'!$B$4:$AD$364="Unemployed"),('Tracking System'!$AX$4:$BI$364=500),'Tracking System'!$A$4:$A$364)

the changes from your formula to mine is:

B to AD
J to AX:BI
A to AB

The range of year one is 4 to 364.

I have tried the formula also (--( with no avail.

Thanks again.

[xld]

You have to have the same range sizes, you have more columns in the first condition than in the second, and when you use multiple columns, use (cond)*(cond)*(numbers) in the SP syntax.

[Domenic]

To sum Column A, where any of the corresponding values in Columns B through AD equals "Unemployed" and any of the corresponding values in Columns AX through BI equals 500, try...

Confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(MMULT((B4:AD364="Unemployed")+0,TRANSPOSE(COLUMN(B4:AD364)^0)),IF(MMULT((AX4:BI364=500)+0,TRANSPOSE(COLUMN(AX4:BI364)^0)),A4:A364)))

Confirmed with just ENTER:

=SUMPRODUCT(--(MMULT((B4:AD364="Unemployed")+0,ROW(B4:INDEX(B4:B364,COLUMNS(B4:AD364)))^0)>0),--(MMULT((AX4:BI364=500)+0,ROW(AX4:INDEX(AX4:AX364,COLUMNS(AX4:BI364)))^0)>0),A4:A364)

Hope this helps!

[freshstart]

Thanks again, worked out like this....

=SUM(IF(MMULT(('Tracking System'!AD4:AD364="Unemployed")+0,TRANSPOSE(COLUMN('Tracking System'!AD4:AD364)^0)),IF(MMULT(('Tracking System'!AX4:BI364=500)+0,TRANSPOSE(COLUMN('Tracking System'!AX4:BI364)^0)),'Tracking System'!AB4:AB364)))