CODEWITHSUNDEEP
excelfunctionsumifs
NHure92
NHure92

Reputation: 105

sumifs syntax for multiple conditions incorrect

I'm currently working from two worksheets, a main data sheet titled 'Equities' which details the stock trading data undertaken on a daily basis. I've attached a screenshot of the column labels below for this worksheet.

enter image description here

I have a monthly commission worksheet which draws information from the equities sheet using sumifs to return results to a relevant date range. I've attached the column labels screenshot below and the date column to show how it is categorised.

enter image description here

enter image description here

=SUMIFS(Equities!L:L,Equities!A:A,">=1/10/2018",Equities!A:A,"<=31/10/2018")

The above sumif returns an accurate result for the gross revenue USD column on the monthly commission worksheet. I have tried to adjust the sumif by adding an additional criteria so as to have monthly figures for Trader 1, 2, 3 etc. 

=SUMIFS(Equities!$N:$N, Equities!$L:$L, Monthly Commission!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018"

The SUMIF above is the one I've edited to include the trader as an additional criteria. However, it has not worked and returns a 0 value, and also prompts me to open a file. I'm not sure where the error is in the syntax or composition of the sumif, so any advice on how to correct it would be greatly appreciated.

Upvotes: 0

Views: 58

Answers (2)

NHure92
NHure92

Reputation: 105

The incorrect column was being summed, answer provided by @XORLX.

=SUMIFS(Equities!$L:$L, Equities!$N:$N, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34370

Because the name of the Monthly Commission sheet has a space in it, you need to put it in single quotes:

=SUMIFS(Equities!$N:$N, Equities!$L:$L, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")

Upvotes: 0

Related Questions