Sheet 1 total consolidation to sheet 2

Sir / Madam, i need to bring total consolidation from sheet1 to sheet 2 and sheet 3, i have highlighted output there like wise, i want to bring the same highlighted output from sheet1 to sheet2 and 3

This formula was not working =SUMIF('Region wise Consolidation'!$K$4:$K$54,'Programme wise'!$B6,'Region wise Consolidation'!K$5) in C6 column

please give me the correct one.

Hi there,

In cell C6 on the Programme wise sheet, try: =SUMIFS('Region wise Consolidation'!$K$4:$K$54,'Region wise Consolidation'!$C$4:$C$54,LEFT(A4,LEN(A4)-FIND(" ",A4)-4))

Basically: Area/range you want to SUM, Area/range where your CRITERIA is, the CRITERIA to match in yourarea/range.

Note: Try to avoid merging cells.
Would be easier if you have the 'Programm' as a criteria in a cell. You have it as xyz..SCALE UP MIS, so we break the first word out with the 'LEFT' function.

its working fine, and i need the same for state wise sheet.. please send me the same to sheet3 also

please workout and send me the excel file for sheet 2 and sheet 3 the formula its not working from C 28 cells

These formula is not working in C28 and C39

=SUMIFS('Region wise Consolidation'!$K$4:$K$54,'Region wise Consolidation'!$C$4:$C$54,LEFT(A26,LEN(A26)-FIND(" ",A26)-4))

=SUMIFS('Region wise Consolidation'!$K$4:$K$54,'Region wise Consolidation'!$C$4:$C$54,LEFT(A37,LEN(A37)-FIND(" ",A37)-4))

Redone formula for 'Programme wise' sheet:

C6 =SUMIFS('Region wise Consolidation'!$K$4:$K$54,'Region wise Consolidation'!$C$4:$C$54,LEFT(A4,FIND(" SCALE",A4)-1))

C28 =SUMIFS('Region wise Consolidation'!$K$4:$K$54,'Region wise Consolidation'!$C$4:$C$54,LEFT(A26,FIND(" SCALE",A26)-1))

C39 =SUMIFS('Region wise Consolidation'!$K$4:$K$54,'Region wise Consolidation'!$C$4:$C$54,LEFT(A37,FIND(" SCALE",A37)-1))

On the 'state wise' sheet:

C6 =SUMIFS('Region wise Consolidation'!$K$4:$K$54,'Region wise Consolidation'!$E$4:$E$54,LEFT(A4,FIND(" SCALE",A4)-1))

Note: your sample had the value of 1 in the yellow cell for Andhra Pradesh. The result I am getting is 160. 1 is only the district of Warangal under the Andhra Pradesh state.