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

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
84
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
Joined
Feb 17, 2018
Messages
84
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
Joined
Oct 14, 2016
Messages
3,711
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
Joined
Feb 17, 2018
Messages
84
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
Joined
Feb 17, 2018
Messages
84
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
Joined
Oct 14, 2016
Messages
3,711
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")
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top