# Using =sumifs's with multiple search criteria and filtered data

#### Graham C1600

##### Board Regular
Hi,

I have a spreadsheet with multiple sheets with multiple data. Until now it has worked really well but i have been asked to provide further MI on the data i already have.

Let's say the layout is as below :_

A B C D E
FOOTBALL SIMON BOY 15 ?????
CRICKET STEVE BOY 13
TENNIS GAIL GIRL 8
RUGBY RUBY GIRL 23
GOLF STEVE BOY 13

Now in E1 i am wanting to find out the combined age of the first criteria (Steve) from column B and the second criteria (Boy) from column C. The result in E1 should be 26.
I'm not sure on the best way to proceed or even the correct function. I also have filters in place so when E1 gets populated it needs to count only visible cells (this isn't shown on the above but on my spreadsheet i have around 20,000 rows of data). My spreadsheet currently shows #N/A when the filters are in place.

Hope this makes sense and i can provide more information if required.

Thanks

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Graham C1600

##### Board Regular
Apologies it doesn't look as it should. There are 5 columns A to E. Column A has the sports in it. Column B has the names in it. Column C has Boy or Girl in it. Column D has ages in it. Column E is where the function is to go.

#### Tetra201

##### MrExcel MVP
If you want to use SUMIFS to sum only visible cells, you would need a helper column (that might be hidden). In the first cell of this helper column (say, column X), enter the following formula and drag-copy it down to the last row of your data:

=SUBTOTAL(102,D1)

Now, you can get your sum by using:

=SUMIFS(D:D,B:B,"STEVE",C:C,"BOY",X:X,1)

#### Graham C1600

##### Board Regular
Thanks for this Tetra201. It works fine and counts when i apply filters. However i want it to count the total of column E without applying the filters (apologies for not making this clear from the start). So is there a way of adding a function to add the column and get the same answer if the filters are applied or not ? I only normally filter to double check numbers so i would like to not have to do this for ease and speed.

Thanks

#### Graham C1600

##### Board Regular
Thanks for this Tetra201. It works fine and counts when i apply filters. However i want it to count the total of column E without applying the filters (apologies for not making this clear from the start). So is there a way of adding a function to add the column and get the same answer if the filters are applied or not ? I only normally filter to double check numbers so i would like to not have to do this for ease and speed.

So my spreadsheet might be 15,000 rows deep and columns A through to D will have loads of #N/A's down the columns based on existing vlookup's.

Hope this makes sense.

Thanks

#### Tetra201

##### MrExcel MVP
The formula sums visible criteria-matching cells.So, it will work both with and without filters applied. If it affects your speed, don't set the helper column up and shorten the formula to:

=SUMIFS(D:D,B:B,"STEVE",C:C,"BOY")

Replies
3
Views
412
Replies
0
Views
398
Replies
3
Views
686
Replies
3
Views
117
Replies
13
Views
450

1,148,108
Messages
5,744,878
Members
423,908
Latest member
Getfour

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back