SUMIFS - color as criteria with other

[Muhammadshoaibasadkhalid]

Hi Gurus,

I need the help to write the VBA code or standard function to do the SUMIFS - with both criteria as colour and value.

Sample data with required results attached

Urgent - response requested.

Regards

Shoaib

 

[bosco_yip]

Try this formula solution by,

1] Define 2 Names :

>> Select B17 >> Define Name >>

>> Name (1) : SumRgn

>> Refer to :
=INDEX($D$5:$I$10,MATCH($A17,$A$5:$A$10,0)+MATCH(LOOKUP("zz",$B$15:B$15),{"Onsite";"OffSite"},0)-1,0)

>>OK

And,

>> Name (2) : SumColor

>> Refer to :
=SUMPRODUCT(0+(GET.CELL(63,IF(1,+OFFSET(SumRgn,,COLUMN($D$4:$I$4)-MIN(COLUMN($D$4:$I$4)))))=GET.CELL(63,B$16)),SumRgn)

>>OK

2] In B17, formula copied across to G17 and all copied down :

=SumColor

p.s. : Get.Cell() is a Excel 4 Macro function, so you need to save file as Macro-Enable Workbook xlsm type.

Regards
Bosco

 

[Muhammadshoaibasadkhalid]

Thanks a lot, it is work perfectly fine, just one more query,

it is not working in my resultant table in the separate sheet. what to do

 

[bosco_yip]

Thanks a lot, it is work perfectly fine, just one more query,

it is not working in my resultant table in the separate sheet. what to do

You need to upload a file showing what you mean by this.

Regards
Bosco

 

[Muhammadshoaibasadkhalid]

hi,

thank you again for your response, file attached only difference is two separate sheets for data source table and result table.

regards

Shoaib

You need to upload a file showing what you mean by this.

Regards
Bosco

 

[bosco_yip]

hi,

thank you again for your response, file attached only difference is two separate sheets for data source table and result table.

regards

Shoaib

See revised formula as per attachment.

Regards
Bosco