SUMIF Help

**el4n** · 08-05-2012, 04:14 AM

I'm trying to create a formula, I think using the SUMIF funtion, but can't seem to get it correct. Have a look at the excel spreadsheet attached to see the issue.

Any help would be great, thank you!

**Ace_XL** · 08-05-2012, 04:35 AM

Use either of the two

=SUMPRODUCT((C9:V9=B6)*C11:V13)
OR
=SUM(IF(C9:V9=B6,C11:V13)) ...array formula -- confirm with Ctrl+Shift+Enter not just Enter

Also, change forumla in Row 9 to =(MONTH(C10)&"."&YEAR(C10))*1 to ensure it is in numeric form

**Richard Buttrey** · 08-05-2012, 07:22 AM

Hi,

If you put the date 1/1/2012 in say B7 then

=SUMPRODUCT((C10:V10>=B7)*(C10:V10<=EOMONTH(B7,0))*C11:V13)

**el4n** · 08-05-2012, 08:04 PM

Thanks everyone!

---------- Post added at 07:04 PM ---------- Previous post was at 04:00 PM ----------

After using these sum and sum related formulas (see above) within my workbook, I'm noticing that they have begun to take a great deal of time to calculate and makes the use of the workbook much slower. I basically have around 30 tabs full of data, probably over 20,000 cells filled with data in each tab. I've tried using all the formulas above to slice the data but it seems these formulas may be too CPU intensive.

Does anybody have a more elegant method to solve my problem above without using a formula that will ultimately bog down the workbook so much?

**oeldere** · 08-05-2012, 08:26 PM

If you are able to change the format of the data, you can use pivot table.

See the example.

And for the change of format see the link below.

http://www.excelforum.com/excel-prog...ose-macro.html

**el4n** · 08-05-2012, 10:23 PM

Originally Posted by oeldere

If you are able to change the format of the data, you can use pivot table.

See the example.

And for the change of format see the link below.

http://www.excelforum.com/excel-prog...ose-macro.html

Thanks, but because I have multiple tabs (over 20) that I'll be pulling data from, and then combining it all into one output tab, I don't think the pivot table will work in this instance

**oeldere** · 08-06-2012, 02:54 AM

And how you get the data in that one output tab?

You work with sumif?

Mayby you better use VBA to collect the data in a masters sheet (output tab).

And after that use a pivot table.

I can't help you enough with VBA.