You Tubers Love Excel#11: Pivot Table, SUMPRODUCT & SUMIFS

See how to add with multiple criteria using a Pivot Table (PivotTable), the SUMPRODUCT function and the SUMIFS function! Also see how to create many formulas all at once by using the correct type of cell references.

Pivot Table (PivotTable) for summing with multiple criteria
SUMPRODUCTS function for summing with multiple criteria (Array Formula)
SUMIFS function for summing with multiple criteria

Category:

Science & Technology

Tags:

• Excel
• Pivot
• Table
• (PivotTable)
• SUMPRODUCT
• function
• Array
• Formulas
• SUMIFS
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[caballero4321]

Hi, I am using following formula =SUMAPRODUCTO(($E$2:$E$5240=$K­$1)*($F$2:$F$5240=F3)*($H$3:$H­$5241)), which does work well, but it does not when I enter wild cards in F3, pls if you could help me how to make it work using wild cards, like I have text values as "ship dock" and I would like it addups all "ship docks", the formula would be wonderful.

Thank you in advance...

[ExcelIsFun]

What version of Excel are you using? 2003, 2007, 2010?

[ExcelIsFun]

In any version something like this should work:

=SUMAPRODUCTO(($E$2:$E$5240=$K­$1)*(SEARCH("ship dock",$F$2:$F$5240)>0)*($H$3:$­H$5241))

[ExcelIsFun]

This excel formula will work in 2007 or 2010:

=SUMIFS($H$3:$H$21,$E$2:$E$20,­$K$1,$F$2:$F$20,"*ship dock*")

[oneggold]

You are amazing with Excel, just please stop with the "knock" sound that you do from time to time (its horrible !!! with headphones)

[ExcelIsFun]

Ya, sorry. I made this video over 2 years ago. Since this video I have made over 1000 more and they are of better quality.

[ExcelIsFun]

I don't know off the top of my head how to do it with a PT. Post your question to the Mr Excel Message Board:

mrexcel[dot]com/forum

I bet you will get an answer within minutes.

[thuynguyenable]

Hello Excellsfun,

My question is about conditional filtering in PV. Sales for A,B,C,D is 500,700,900,1100&Profit eachperson makes is900,1200,1600,2000.I want to list thoseSalesReps thathaveSales>600 & Profit>$1250. But it seemsthat PV can only filter either one of the conditions and cannot do both. Can you help me? Thanks.

[ExcelIsFun]

I just tried to download it and was ok.

[dada540]

hi, i was trying to download this workbook

at "WorkbookYouTubersLoveExcel9-1­2StartFile", however the downloaded one does not correspond to this vedio...