SumIF and eliminate blank rows

[jkktx81]

I am wanting to sum rows of data, some cells text and some numbers from a data base sheet to another sheet.

The data base sheet has name and other information for that name aling the cells in that row.

In a seperate sheet would like to bring over some rows of information based off certain critera.
(I can add a new cell in each row that would specify this to be summed in the seperate shet) Such as a "yes" in a cell next to the name...)

The part I don't know hiw to do is in the seperate sheet I don't want blank rows as onlt some of the rows from sheet1 will be wanted on sheet2

Is there a way to do this without VBA? A formula that will do this so we don't have to copy and paste all the time?
And I don't like the copy and paste becuase sometimes the data will change on sheet1 and that needs to reflect on sheet2

I just don't want to have a lot of blank rows on sheet2

[mikeTRON]

I would use a pivot table.

[FDibbins]

Hi, welcome to the forum

I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[jkktx81]

I would rather this not be a pivot table beucase of the application use by several people.

I don't see how to upload an actaul excel file.

[jkktx81]

Picture1.png a picture is all I could upload

I just did a mock. But is basically the same. The yes in column B of sheet1would indicate that row of information needed to be on sheet 2, but not in same row, becuase wanting to eliminate the rows noy in use on sheet 2 since there is a lot more no's than yes's in sheet1.

[mikeTRON]

I would rather this not be a pivot table beucase of the application use by several people.

I don't see how to upload an actaul excel file.

I dont understand the aversion to a pivot table because others use it as well.
Also look at my signature for how to upload a file.

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[jkktx81]

The reason it's an ongoing updated presentation sheet and the visual of this sheet is important and doesnt work as needed using pivot tables.

We might have to continue to pull the data into a seperate workbook for the weekly presenting of information, but was hoping for a better alternative using formulas and not VBA.

[FDibbins]

OK, so what do you want to pull in, and based on what?

Assuming you want to pull out all rows with YES, try this on sheet 2, B4, copied down and across...
=IFERROR(INDEX(Sheet1!B$4:B$29,SMALL(IF(Sheet1!$C$4:$C$29="Yes",ROW($B$4:$B$29)-3),ROWS($A$1:A1))),"")

This is an ARRAY formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.