CODEWITHSUNDEEP
excelsumifs
Ashley McGraw
Ashley McGraw

Reputation: 1

SUMIF based on another worksheet name and month

I'm trying to write a formula and I can get it to work in parts, but not all together. I think I might need an array formula, with which I have no experience.

The formula should pull data from worksheet "2017". I want it to sum any numbers located in column R IF the value in column F or G of that row is "Name" AND the date in column N is in the month of January.

Upvotes: 0

Views: 166

Answers (2)

pnuts
pnuts

Reputation: 59485

I seriously misread the Q at my first attempt. What I ought to have suggested is:

=SUMIFS(R:R,F:F,"Name",N:N,">=42736",N:N,"<42767")+SUMIFS(R:R,G:G,"Name",N:N,">=42736",N:N,"<42767")-SUMIFS(R:R,F:F,"Name",G:G,"Name",N:N,">=42736",N:N,"<42767")

Not elegant but uses just the SUMIFS function to get to the result by adding all the cases where the "F" criterion applies to all the cases where the "G" criterion applies and subtracting the cases where both "F" and "G" criteria apply.

42736 and 42767 are the serial numbers in the 1900 date system for the start of January 2017 and the start of February 2017. The 'or equals' is to allow for the possibility that column N might include times.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152605

SUMIFS() do not like the OR when looking two different ranges so you will need to use SUMPRODUCT and limit the range to only the dataset:

=SUMPRODUCT(R1:R100,((G1:G100 = "Name")+(F1:F100="Name")>0)*(MONTH(N1:N100) = 1))

as per your comments your formula should be:

=SUMPRODUCT('2017'!R1:R100,(('2017'!G1:G100 = "Allison Jones")+('2017'!F1:F100="Allison Jones")>0)*(MONTH('2017'!N1:N100) = 1))

Avoid full column references as this is an array type formula and it will slow down the calculations.

Upvotes: 1

Related Questions