Excel 2007 : Help with SUMIF please!!

[Matabele]

Hi everyone,

Im in major need of some help please. I am simply trying to sum the counts of bat calls that fall on a particular date. The mother data set contains three separate species. I am wanting to lump Molossid and Vesper counts and seperate Miniop counts, but total each group for a particular date.

Please attached spreadsheet. The group of data on the right is working perfectly with the forumla I have put in, its giving me exactly what I want and lumping Molossid and Vesper counts per date. The Miniop set of data on the left though is not working for some reason. I have applied the same formula to the same set of data in the same format but its not working.

Can anyone help me to get this to work please?

Help.xlsx

[Fotis1991]

Ηι. Welcome to the forum.

Maybe try to "lock" the ranges....

=SUMIF($A$1:$A$54,"="&D1,$C$1:$C$54)

[Matabele]

Hi Fotis!

Thanks for the welcome Thanks also for your help, I tried your suggestion but it doesn't seem to have fixed the problem. Also the forumula used on the data set on the right did not need locked ranges. I'd appreciate any other suggestions though.

[Cutter]

You could just use whole column:

=SUMIF(A:A,D1,C:C) (notice you don't need the "="&)

[Matabele]

Hey cutter, thanks for your reply. Now this is weird. I applied your change to the right set of data and sure enough the values remained the same and it worked fine, so yes I didn't need to use the "="& bit. But when I tried to apply the change to the left hand set of data, it didn't work. It gives the correct answer for the first date and then just 0's after that. Any ideas?

[Fotis1991]

I angree with Cutter, that you don't need the &= signs...

But even Cutter's, even mine gives you results. Not 0 !!

[Cutter]

I am seeing various results in column E as well.
What formula do you have in E1 and did you drag it all the way down to E160?
If you put =SUM(C:C) in some unused cell and =SUM(E:E) in another they should both equal 64

[Matabele]

Guys thanks for your help I appreciate it. I really dont know whats going on, Im wondering if its a formatting issue? Cutter the formula in E1 in my spreadsheet is =SUMIF(A1:A54,"="&D1,C1:C54) and yes I dragged it all the way down to E160.

I tried doing =SUM(C:C) and got 64, but got a value of 1 for =SUM(E:E) (i.e. summing from E1:E160) in the spreadsheet I attached. I tried SUM(E:E) in the spreadsheet Fotis attached, which apprears to be working, and got a value of 38.

Any ideas?

[Matabele]

Maybe I should add that Im using the filter option from the "sort and filter" button to highlight all data by name. So selecting all data for Miniop for example in the main data set. Then copying and pasting these data into a new worksheet, just straight paste not special paste. Then doing the same thing for Vesper and Molossid data. Just wondering if the filter option is messing up cell counts or something?

[Fotis1991]

AS you said "appears to working"


Did not worked, because i had not drag down and did not locked it, corectly.

Corect result is 64

[Matabele]

Hi Fotis, it appears to be working for you no problem. I applied the same formula on the spreadsheet you just attached, please see column "P" in the attached spreadsheet. Its giving me zeros! I didnt do anything other than apply the formula, drag it down an save the worksheet. Im sure its correct but can you check the formula please.

0Help (2)(1)a.xlsx

[Fotis1991]

It's because, you don't LOCK your formula

Your formula in P1, is this.

=SUMIF($A1:$A54,D1,$C1:$C54)

You need to do this.

=SUMIF($A$1:$A$54,D1,$C$1:$C$54)

[Cutter]

Or just use the whole column formula like I suggested in post #4 if your ranges will be growing.

[Matabele]

Haha, it works! Thank you Fotis, you have really helped me out here!! I needed to open my eyes more and "see" what you were telling me Thanks again for your time!

[Matabele]

Thanks also Cutter for your help, much appreciated. Yes the column approach does seem to work too.

[Fotis1991]

Again my poor English....

You are welcome.

OF course, Cutter's formula, works too.

@ Cutter.

I had include your suggestion to my attachments.