# Excel sumif function or countif

peug417 20:41 02 May 06
Locked

Hi guys i will try and make this as clear as possible..
I am working on linking the contents of one sheet to another...... I have succesfully used the countif function to count the number of times the product is mentioned in a column.

ie = countif(sheet1!\$l\$4:\$l\$16,"stella")

However i am having difficulty with the next stage. My cell has to sum a seperate column containg the qty of a product.

i have tried
=IF(sheet1!\$L\$4:\$L\$26="stella",SUM(sheet1!\$G\$4:\$G\$26),0)

I may well further on in the project require a third check looking up a text match but lets see if i can get over this hurdle first..

VoG II 20:45 02 May 06

=SUMPRODUCT(--(L4:L26="stella"),(G4:G26))

peug417 21:09 02 May 06

i am asumming the (--( is for the sheet name?
in which case should there not be one before g4:g26) I have tried all ways still getting errors.

VoG II 21:14 02 May 06

If you are entering this formula on a different sheet then you need the full path:

=SUMPRODUCT(--(Sheet1!L4:L26="stella"),(Sheet1!G4:G26))

The -- is to force coercion of the result of (Sheet1!L4:L26="stella") to produce a number rather than a logical value. The alternative is

=SUMPRODUCT((Sheet1!L4:L26="stella")*(Sheet1!G4:G26))

but this is slower than the -- example.

VoG II 21:39 02 May 06

Incidentally, you could also use an array formula

=SUM(((Sheet1!L4:L26="stella"))*(Sheet1!G4:G26))

confirmed by pressing CTRL + Shift + Enter which will then display as

={SUM(((Sheet1!L4:L26="stella"))*(Sheet1!G4:G26))}

in the formula bar. However, this too is 'more expensive' in required computing power.

peug417 22:02 02 May 06

Thanks mate that bit is digested and working correctly now.the final stage of the puzzle is to veryfy the content of another cell before returning a value.
if column h4:h26= either "Keg" or "Can"

so for eg cell a numerical value will only be calculated if cell L4 = "stella" and cell h4 = "Can".

hope that makes sense

VoG II 22:33 02 May 06

=SUMPRODUCT(--(Sheet1!L4:L26="stella"),--ISNUMBER(MATCH(Sheet1!H4:H26,{"keg","can"},0)),Sheet1!G4:G26)

VoG II 20:12 04 May 06

Did it work?

peug417 20:17 04 May 06

Sorry mate I Have been busy and couldn't get on last night. It worked in a test environmet ie. in a new workbook linking Sheet 1 to sheet2. In the realtime version where different values arte required I am not having much success. Its difficult to post the real values due to sensitivities. My mate Whisperer must be away at present as i usually email him..

peug417 21:10 27 May 06

I AM CLOSING THIS THREAD FOR TWO REASONS.
1 vOG'S RECOMENDATIONS WORKED IN THE TEST ENVIRONMENT, AND 2 THE COMPANY HAVE CHANGED THE LAYOUT OF THE FORM. SO I NEED TO STUDY IT AND MAYBE REPOST.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…