# SUMIFS used with month ?

1. ## SUMIFS used with month ?

I need to sum all the numbers in J column, where the F column is "Solgt" and the month in column H is August.

What I have done so far is this - but it does not work. I guess that it has something to do with how I used the month function.

=SUMIFS(Sheet1!J:J;Sheet1!F:F;"Solgt";Sheet1!H:H;MONTH(8))

My date is in this format - 01-12-2013

Anyone who can help ?

- Kim -  Register To Reply

2. ## Re: SUMIFS used with month ?

Is it just August in 2013? If so you can specify start and end date, e.g.

=SUMIFS(Sheet1!J:J;Sheet1!F:F;"Solgt";Sheet1!H:H;">="&DATE(2013;8;1),Sheet1!H:H;"<"&DATE(2013;9;1))  Register To Reply

3. ## Re: SUMIFS used with month ?

Perhaps..

=SUMPRODUCT((Sheet1!F2:F1000="Solgt")*(MONTH(Sheet1!H2:H1000)=8)*(Sheet1!J2:J1000))  Register To Reply

4. ## Re: SUMIFS used with month ? Originally Posted by daddylonglegs Is it just August in 2013? If so you can specify start and end date, e.g.

=SUMIFS(Sheet1!J:J;Sheet1!F:F;"Solgt";Sheet1!H:H;">="&DATE(2013;8;1),Sheet1!H:H;"<"&DATE(2013;9;1))
Yes, it is only august in 2013. I forgot to tell that   Register To Reply

5. ## Re: SUMIFS used with month ?

Thanks to both of you I'll take a look at it - Kim -  Register To Reply

6. ## Re: SUMIFS used with month ?

This should work for you.

Formula:  `Please Login or Register  to view this content.`  Register To Reply

7. ## Re: SUMIFS used with month ? Originally Posted by Fotis1991 Perhaps..

=SUMPRODUCT((Sheet1!F2:F1000="Solgt")*(MONTH(Sheet1!H2:H1000)=8)*(Sheet1!J2:J1000))
Should I think of the * as an AND ? When I do some tests, it seems to me that it works like AND - just asking to be sure.

- Kim -  Register To Reply

8. ## Re: SUMIFS used with month ?

* is to multiply  Register To Reply

9. ## Re: SUMIFS used with month ?

I'm a bit confused now. I know what multiplying is - but in this case I can't seem what multiplying have to do with this.

Multiplying the word "solgt" with a month and then multiply with some numbers - does not make any sense to me.  Register To Reply

10. ## Re: SUMIFS used with month ?

Yes, you are correct * gives an AND effect (+ gives an OR effect) but it does multiply. When you use a condition against a range like this

(Sheet1!F2:F1000="Solgt")

Then the result is an array of TRUE/FALSE values and when you multiply two or more of those arrays together the TRUEs becomes 1s and the FALSEs become zeroes so the resultant array has 1 and zero values, 1s only where all conditions are TRUE. If you then multiply that array by the "sum range" you will effectively sum rows only where all conditions are TRUE, so the effect is similar to SUMIFS

see here for an explanation of SUMPRODUCT, particularly with regard to multi-conditional tests  Register To Reply

11. ## Re: SUMIFS used with month ? Originally Posted by daddylonglegs Yes, you are correct * gives an AND effect (+ gives an OR effect) but it does multiply. When you use a condition against a range like this

(Sheet1!F2:F1000="Solgt")

Then the result is an array of TRUE/FALSE values and when you multiply two or more of those arrays together the TRUEs becomes 1s and the FALSEs become zeroes so the resultant array has 1 and zero values, 1s only where all conditions are TRUE. If you then multiply that array by the "sum range" you will effectively sum rows only where all conditions are TRUE, so the effect is similar to SUMIFS

see here for an explanation of SUMPRODUCT, particularly with regard to multi-conditional tests
Now I see what Alkey meant, when he told me that * is the same as multiplying. Thanks - Kim -  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1