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

[Graham C1600]

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

 

[Graham C1600]

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]

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]

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]

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]

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")