My formula =IF(Countifs

Copper Contributor

My formula =IF(Countifs(sheet2!C;C, Sheet1!A2)>0, SUM(sheet2!D3-T3), “NO DATA”) only works when sheet1 and sheet 2 data lines up in a row. If the array in sheet 2 is not lined up with sht1, it doesn’t work. I need it to match sheet2 row with sheet1 and then sums D-T data from sht 2

5 Replies

@Pingger 

Now you subtract value of T3 from Sheet2!D3, actually your formula is

=IF(Countifs(sheet2!C;C, Sheet1!A2), sheet2!D3-T3, “NO DATA”)

Or you'd like to work with entire column D in Sheet2?

Thanks for looking, Sergei.

I need to SUM data in D through T, hence D-T

As a Match is found in Sheet 1, I want the sum of data in sheet 2 in row D through T. This condition works only if sheet 1 A1 and sheet 2 C:C lines up row for row, in other words if Ax is lined up in Cx by row. I need it to match whether sheet1 A58 is in sheet2 C15, sheet1 D58 will sum D15 to T15

Let me know if you have further questions,

Thanks
Pingger

@Pingger 

Pingger, if I understood your logic correctly, in Sheet1 here

clipboard_image_0.png

it could be formula

=IFNA(SUM(INDEX(Sheet2!D:T,MATCH(Sheet1!A1,Sheet2!C:C,0),0)),"no such")

which sums D to T for found row in Sheet2

clipboard_image_1.png

 

Please check in attached file.

Sergei, just checked your formula and it works beautifully, thanks you very much.

@Pingger , glad to help