SUMIF Returns zero - Why? 1

[MeGustaXL]

Hiya,

Working in XL 2K3 I've got a set of tables like this:

Task M T W T F Total
Job1 1 0 0 0 0 1
Job2 0 1 3 0 0 4
Job3 0 0 1 2 3 6
Job4 6 1 0 3 0 10
Job5 0 5 3 2 4 14
Total 7 7 7 7 7 35

Task M T W T F Total
Job1 0 0 0 0 3 3
Job2 0 1 3 0 0 4
Job3 1 0 1 2 0 4
Job4 6 1 0 3 0 10
Job5 0 5 3 2 4 14
Total 7 7 7 7 7 35
etc...

I'm trying to use SUMIF to add up the individual "Task" totals, like this:

={SUMIF(A:A,"Job1",G:G)}, repeated for Job2, Job3, etc. So in this example, I want the formula to return "4", as there are 2 Totals for Job1 of 1 and 3, and ={SUMIF(A:A,"Job2",G:G)} should be 8.

But the formula returns zero! What is wrong?

Chris

Someday I'll know what I'm donig...damn!

 

[Loomah]

hi
what you've used works fine for me but without the curly brackets "{}". not sure why you have them in there?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[MeGustaXL]

Hi Loomah,

That's cos I entered it as an Array formula - is that all that's wroong do you think?

I wondered if it was because the Totals are calculated cells, not straight numbers?


Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

hi,

The tables that you have displayed are pivots or a summary of some source data. For instance...
[tt]
JobName WeekDay Value
Job1 M 1
Job2 T 1
Job2 W 3
Job3 W 1
Job3 T 2
Job3 F 3
Job4 M 6
Job4 T 1
Job4 T 3
Job5 T 5
Job5 W 3
Job5 T 2
Job5 F 4
[/tt]
However, WeekDay is of little use unless your corporate data ONLY refers to one week and all previous weeks' data is summarily discarded, into the memory hole, forever forgotten, never to be remembered, of no material value!

A hugely better SOURCE to do the kind of summing that you have described, would be JobName, JobDate, JobValue.

From THAT kind of table, you can generate the table displays similar to your examples AND summarize, using SUMIFS() in 2007+ or SUMPRODUCT() in 97-2003.

Bottom line: Using an aggregation table is NOT the ideal source for doing other aggregations. Use a well designed DETAIL SOURCE TABLE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MeGustaXL]

Hi Skip, and thanks for your input.

Yeah I realise it's not the best way to do it, however it's the Company Way to replicate the table for each week, thus creating a new, duplicate table for every week of the year.

Previous weeks are not forgotten, or thrown into the memory hole, but saved as a record of what you worked on in the past, then filed in a folder in hardcopy forever.

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

I repeat, and STRONGLY suggest NORMALIZING the weekly summary data and COMBINING with previous data to have ONE TABLE for the purpose of analysis & summarization! This kind of table is what Excel was designed to use.

faq68-5287

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

For me both formulas work (array and non-array).
Check formats and values in column G, if any of them is text, the cell is treated as 0.
Search for empty spaces after Jobx (select column and search & replace: search for ' ', replace by nothing).

combo

 

[MeGustaXL]

Thanks Combo - that was the problem indeed!
Column G has the "Total" headings where shown - get rid of them and it works

Have a STAR!

Chris

Someday I'll know what I'm donig...damn!