Sumif problem

[Gerry Cornell]

What is wrong here please?

=SUMIF($D$26$41,"C",SUMIF($B$26:$B$41,"*9.10.06",$H$26:$H$41),0)

TIA


--

~~~~

Gerry

~~~~~~~~
Enquire, plan and execute.
Stourport, England
~~~~~~~~~~~~~~~~~

 

[aidan.heritage]

SUMIF wouldn't like that - sumif is
SUMIF(rangetotest,test,optional range to sum)

I THINK you want something along the lines of

=sumproduct(--($d$26$41="C"),--($B$26:$B$41="*9.10.06"),$H$26:$H$41)

 

[Roger Govier]

Hi Gerry

If you are using XL2007, then you could use SUMIFS().
Note however there is a difference in the order of the ranges compared
with SUMIF().
You have to give the range to be summed First (not third), followed by
your different criteria

=SUMIFS($H$26:$H$41,$D$26$41,"C",$B$26:$B$41,"09.10.06")

If you are using versions before XL2007, then you already have a
solution using Sumproduct

 

[Gerry Cornell]

Aidan

Thanks for responding.

Your suggestion produces an error. The $H$41 is highlighted.

I have not previously used sumproduct and I have never managed to
master arrays. Can you please point me to what is wrong.

One thing. I cannot see how Excel knows to look in $C$26:$C$41 for
*9.10.06"?

TIA


--

~~~~

Gerry

~~~~~~~~
Enquire, plan and execute.
Stourport, England
~~~~~~~~~~~~~~~~~

 

[Gerry Cornell]

Thanks Roger for responding.

Still using XL2000.

I have tried the suggested solution but there is a bug as you will see
from my reply to Aidan.

Thanks for your interest.


--

Regards.

Gerry
~~~~
FCA
Stourport, England
Enquire, plan and execute
~~~~~~~~~~~~~~~~~~~

 

[aidan.heritage]

Sumproduct is very useful, and something I picked up here - basically,
you are testing each individual block of cells and returning a true or
false value (which are converted to 1's and 0's using the -- before the
brackets) - so you will end up with (if both statements are true)
=1*1*actualvalue
but if one or more statements are FALSE then a zero appears - which
will reduce the result to zero - excel won't look in C26 to C41 with
the formula I gave you as you referred to B26 to B41, but it's easy
enough to change!

 

[Dave Peterson]

Your original formula looked like:
=SUMIF($D$26$41,"C",SUMIF($B$26:$B$41,"*9.10.06",$H$26:$H$41),0)

And that looked like B26:B41 is the range that should be compared to "*9.10.06".

Aidan's response:
=sumproduct(--($d$26$41="C"),--($B$26:$B$41="*9.10.06"),$H$26:$H$41)

looks for C in D2641
and *9.10.06 (the text--not a date) in B26:B41

When both are true on the same row, it'll use the value in H26:H41.

If this isn't what you want, you'll want to share your requirements (and the
formula that failed).

And some notes...

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html