Sumifs formula

[markster]

Hi there

I need some help with a formula

I'm working with sheet A and Sheet B

Sheet A has the following column headings

Department (A7)
Aug-17 (B7)
Sep-17 (C7)
Oct-17 (D7)
Nov-17 (E7)
Dec-17 (F7) etc. etc.

Sheet B has the following data columns

Department (A7) - data is A8 to A12,000
Amount (B7) - data is B8 to B12,000
Month (C7) - data is C8 to C12,000




So basically I need to populate each of the cells on Sheet A with sum of amounts per month so basically it first looks at the department, looks at the month and then brings data from shee B. I think it's a SUMIF formula but can't get it to work.

Can anyone help?

Cheers
Mark

 

[steve the fish]

Re: Sumifs formula help

With dates you have to be certain what you are looking at is true or formatted to look different. So, that said, in sheet A click into cell B7. What do you see in the formula bar? Do the same with Sheet B cell C8. Paste back here what the formula bar shows.

 

[steve the fish]

Re: Sumifs formula help

You could just test this placed in Sheet A cell B8:

=SUMIFS(SheetB!$B$8:$B$100000,SheetB!$A$8:$A$100000,$A8,SheetB!$C$8:$C$100000,">="&B$7,SheetB!$C$8:$C$100000,"<"&EDATE(B$7,1))

 

[markster]

Re: Sumifs formula help

With dates you have to be certain what you are looking at is true or formatted to look different. So, that said, in sheet A click into cell B7. What do you see in the formula bar? Do the same with Sheet B cell C8. Paste back here what the formula bar shows.

Hi thanks I've used a formula to fix the date format to MMM-YY

 

[steve the fish]

Re: Sumifs formula help

So my formula didnt work? You want your header dates as true dates. So Aug-17 is really 01/08/2017 formatted to look like Aug-17. The formula should then work assuming the Sheet B table has true dates.

 

[markster]

Re: Sumifs formula help

Hi thanks I've used a formula to fix the date format to MMM-YY

Sorry - just out of a meeting now. will give it a try. Thanks very much for your help. Much appreciated.
M

 

[markster]

Re: Sumifs formula help

Sorry - just out of a meeting now. will give it a try. Thanks very much for your help. Much appreciated.
M

Formula works a treat thank you.
M