SUMIF() assistance needed 1

[Scott24x7]

All,
Hi again. Have hit my head against another twister... I am using SUMIF() to SUM up a column of numbers when a person's name matches. So, for example, I have:

=SUMIF(B10:B35,A1,L10:L35)

In this case A1 = "Bill", and B contains the list of items, and L contains the values to calculate. That was all fine. However, I now need to add the condition of "By Month". So in this case, if I want to just sum up the items that have "Bill" as the name, AND are from February, in the same two ranges, where DATE is in column E. Is it possible to use multiple criteria in a "SUMIF", or is there some other function I should use? I assume I'm going to have to some how "pluck out" the month as a value from the date field as well, and check against it, which I can do, but I'm having a big problem figuring out how to get SUMIF to accept my multiple criteria.

Many thanks in advance.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[RivetHed]

Scott,

To calculate a multiple criteria you have to use a SUMPRODUCT function the syntax for what you're trying to do would be something like this:

=SUMPRODUCT(($B$10:$B$35=A1)*(MONTH($E$10:$E$35)=2)*($L$10:$L$35))

This will check Col B for the value in A1, col E for a month equal to 2 and then sum the values in Col L for matching rows.

 

[Scott24x7]

Rivet,
I see where you are coming from, but why the * (multiplication function)? I read SUMPRODUCT and it doesn't indicate it, rather seperates by ",". Also, this didn't work, but I think partly because the first statement ($B$10:$B$35=A1) doesn't make any sense. There is no function that I see in front of it that would eliminate or exclude to records only equalling A1 value. From what I read, SUMPRODUCT on it's own has no "magical power" to exclude data based on criteria, rather it is looking for a list of arrays to multiply and add the products together.
Any other ideas?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[RivetHed]

It's kind of an unlisted application of the sumproduct formula. It tests it row by row and if it gets a match in each section it records a 1 and if it doesn't it records a zero so clearly any zeros render the whole line value to be zero. The final section is not trying to match anything so will use the value in the relevant cell.

I've just dropped a load of dummy data into a worksheet and that formula works fine for me. Search this site for SUMPRODUCT and you'll find loads of threads probably explaining it a little better than I am.

 

[xlbo]

search this forum for SUMPRODUCT - there are many posts that explain how it works

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Scott24x7]

Rivet,
It worked! Funny enough, I was getting 0, and it is only just now I realized that the one name I was pointing to didn't have any activity in February, only in January. So, once I fixed what I had broken trying to make it work again, it returned the value I wanted.
Many thanks!


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."