SUMIF with multiple criteria

[Anne Troy]

I never seem to get this right...or did they change the formula?

I want to change the following formula to sum ONLY IF there is an "A" in column A.

="MST Ob: $"&SUMIF(B4:B155,"MST",H4:H155)

I hope that someday this comes as second nature like many other formulas, but to me vlookup was much easier. I don't understand the sumproducts and the hyphens...

 

[Aladin Akyurek]

If the suggestion does not work, try to post a scaled-down Excel-readable sample directly here in the forum. Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit (Note that an image or a link to a storage site like dropbox or onedrive etc. does not provide a permanent record.). Mind you a forum without a permanent record is not of much value.

 

[Roo007]

https://app.box.com/s/1985dkaexru6yov6qphly5b9pi4dyz8o

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Books	Magazines	Notes	Total
2	x			20
3		x	Aux.	10		Criteria 1:	Criteria 2:	Total
4				25		X	Aux.	25	SUMIFS(D2:D10,A2:A10,"="&F4,B2:B10,"="&F4,C2:C10,""&G4)
5	x			32				138	SUMIFS(d2:d10,b2:b10,"="&f4,c2:c10,""&g4)+SUMIFS(d2:d10,a2:a10,""&f4)
6		x		25				25	SUMIFS(D2:D10,A2:A10,"X",B2:B10,"=X",C2:C10,"*Aux*")
7	x			36				25	SUMIFS(D2:D10,B2:B10,"="&F4,C2:C10,""&G4)
8	x			65				113
9				32		103 is the correct total that I'm looking for.
10				21		So count all of the totals for books that dont have an X, so long as the Magazines do not have an X with a Aux. in Notes
AND and OR

 

[Aladin Akyurek]

@Roo007

Try to post the input and the desired output, not a formula results...

 

[Roo007]

I don't understand. I've done everything that you've asked me to. Are you able to assist me?

 

[Aladin Akyurek]

I don't understand. I've done everything that you've asked me to. Are you able to assist me?

In H you are showing figures. Are these desired figure or figures the suggested formula calculates?

 

[MARZIOTULLIO]

Hi

h4=SUMPRODUCT((A2:A10<>F4)*(B2:B10&C2:C10<>F4&G4)*(D2:D10))

 

[Roo007]

Formulas that I've tried

 

[Aladin Akyurek]

Formulas that I've tried

May I ask you just to repost the input with the desired values in H?

 

[jtakw]

Formulas that I've tried

Hi,

Did you try MARZIOTULLIO's formula in post#16?

I've tested it and it works.

 

[Roo007]

Sorry, i just got home from my meeting. I did try it, & yes it does work. Thank you guys!