solutions for using the SUMIFS and Frequency functions together

[c.lake]

Hi All,

I am currently trying to use excel to sum up multiple criteria within a table, however one of the criteria requires (or so i believe) the use of the frequency function too. Would anyone be able to make any suggestions as to how to solve this?

Further detail:

The table houses a column for Name, Days and dates. I need it to find the matching "name" with matching "dates" (not all are the same) and then add the "days" that coincide with these criteria together.

The formula i have used so far will sum the "days" that correspond with the "name" but can't account for changing "dates", current formula is below;


=SUMIFS(Table1[days used], Table1[Name], "Mo", Table1[Valid From], C3)

Any help is greatly appreciated

[AliGW]

Welcome to the forum.

Please see the yellow banner at the top of the page.

[c.lake]

Hi Ali,

I'm assuming you are suggesting I add an excel sheet to represent the issue, if so then i have attached one here

[FlameRetired]

Not sure I am grasping the concept(s).

I'll start with this.

Please Login or Register  to view this content.

[c.lake]

Hi Flame,

Thanks for the assistance with this problem. Unfortunately this formula will only work when the date within "Valid from" is today's date, so when the spreadsheet gets populated with more varied data it will not return an accurate result.

[c.lake]

In an attempt to simplify this issue somewhat, I have added an additional column so that there isn't a need to distinguish between unique and recurring dates.

I hope this aids in finding an answer

[FlameRetired]

There is no difference in the latest upload.

[AliGW]

Your days used calculations are incorrect - row 3 should be 5 and row 5 should be 20. Notwithstanding this manual error, try this:

=SUMPRODUCT(Table1[Date out]-Table1[Date in],(Table1[Item number]=Table1[@[Item number]])*(Table1[Valid From]=Table1[@[Valid From]]))

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[CARACALLA]

Why ?

D3 in your formula =SUMIFS(Table1[days used], Table1[Name], "John Doe", Table1[Valid From], D3)

try
=SUMIFS(Table1[days used], Table1[Name], "John Doe", Table1[Valid From],E3)

[c.lake]

Hi Ali, Cara,

Unfortunately Ali the formula you suggested returns a "Value!" error

And Cara, since i posted the original formula being tested the spreadsheet was altered (additional column added) in an attempt to simplify the problem.

Thanks all for your continuous efforts to find a solution....this one really doesn't want to make it an easy fix

[c.lake]

Hi Ali,

From playing around with the formula you suggested on 27/11/19 I have managed to get a nearly working format, the below formula is the one i am currently sitting on. From what i can tell the issue that remains is with the OR function (or +) that distinguishes the item numbers in the formula, i believe this to be the problem as it provides a result that using all "John Doe" data regardless of its "item number". Hopefully this is some more to go off