Finding how many times a name appear in 4 sheets.. hard one! :)

[xatomicx]

Hi everyone,

I have 5 differents sheet in my excel file, i need to find a formula that will look into the sheet 1 to 4, and each time they see a specific name like "bobby", it count +1 on a "total" sheet on sheet 5.

I don't know if it's clear for you guys, but i would appreciate a hint or some help on how to create this formula

[scottylad2]

can you be a little clearer, where is the name likely to appear? is it in one column of names per sheet or anywhere at all? are the names contained within ranges?

uploading a sample may help

[xatomicx]

The name will appear only in column A of each 4 sheets, the range the name might appear is A1 to A8000, does it help?

[martindwilson]

since its only 4 sheets why not just =countif(sheet1!a1:a8000,"bobby")+countif(sheet2!a1:a8000,"bobby")+countif(sheet3!a1:a8000,"bobby")+countif(sheet4!a1:a8000,"bobby")

[xatomicx]

I'm trying it right now, doesnt seem to work, let me tweak it a bit and change the names for the real ones and sheet name, shouldnt be a problem then.

[scottylad2]

SUMPRODUCT(--(Sheet1!$A$1:$A$8000=Sheet1!C2)--(Sheet2!$A$1:$A$8000=Sheet1!C2)--(Sheet3!$A$1:$A$8000=Sheet1!C2)--(Sheet4!$A$1:$A$8000=Sheet1!C2)) where Sheet1!C2 has bobby in it

[martindwilson]

dont want to be picky but whats the point of using sumproduct when countif will do?
and as it stands needs , between the arguments
also this would only count where say bobby was in the same cell on all 4 sheets
SUMPRODUCT(--($A$1:$A$1000=C2),--(Sheet2!$A$1:$A$1000=Sheet1!C2),--(Sheet3!$A$1:$A$1000=Sheet1!C2),--(Sheet4!$A$1:$A$1000=Sheet1!C2))

[scottylad2]

just showing there's more than one solution, both will do. Some people prefer sumproduct for counting, although countif is probably better in terms of performance.

The beauty of Excel, many ways to crack a nut

[scottylad2]

also this would only count where say bobby was in the same cell on all 4 sheets

The reference to Bobby is only in cell C1 sheet 1 for my example. he can be anywhere within the ranges of the sheets 1 to 4 in A1:A8000 range,Cell C1Sheet1 is mereley the criteria but it could also be replaced with the the name hardcoded into the formula if it is going to be the only name looked for but if there's more than one then i tend to try and keep the names out of the actual formula and refer to them in a cell

[martindwilson]

but there were no separators in the one you posted ! it wouldn't work! and as i said it wont count all instances of whats in c2 but only where say a5 on each sheet had the value of c2 in it so bobby in a5 of each sheet would count as 1 not 4, also if bobby was in only a100 of sheet 1 it wouldn't get counted at all. now that is possibly what the op wants but we don't know!
ah i see what you're doing but the -- is not needed any way it just confused me! it looked incomplete
=SUMPRODUCT(($A$1:$A$1000=C2)+(Sheet2!$A$1:$A$1000=Sheet1!C2)+(Sheet3!$A$1:$A$1000=Sheet1!C2)+(Sheet4!$A$1:$A$1000=Sheet1!C2)) serves the same purpose

[xatomicx]

Thank you very much everyone, it worked with the countif i never tried the sumproduct function, but i saved that in my note in case i might need it!!!

I want to crank it another level...

heres the real formula that worked:
=COUNTIF('hdcore1 VBA'!A1:A8000;"Rancourt Marc-Olivier")+COUNTIF('hdcore2 VBA'!A1:A8000;"Rancourt Marc-Olivier")+COUNTIF('hdcore3 NON-VBA'!A1:A8000;"Rancourt Marc-Olivier")+COUNTIF('hdcore4 NON-VBA'!A1:A8000;"Rancourt Marc-Olivier")

My sheets are called
hdcore1 VBA
hdcore2 VBA
hdcore3 NON-VBA
hdcore4 NON-VBA

What if...

now that i can count how many times this name appear in sheet 1 to 4, can i, each time excel find a name in column a1 to a8000, it will look at column E (same row as where the name was found), and if row E shows the word "valid" it will count +1 ?

Tricky isnt it?

[martindwilson]

now you need sumproduct!

[xatomicx]

ahaha

how can i use sumproduct then? i never used that function!

[scottylad2]

Which version of Excel do you have? just for clarity, you only want the count now if both criteria are met?

SUMPRODUCT(--($A$1:$A$8000="bobby"),--($E$1:$E$8000="valid"))+SUMPRODUCT(--(Sheet2!$A$1:$A$8000="bobby"),--(Sheet2!E1:E8000="valid")) etc through your 4 sheets.

If you have Excel 2007 or later then Countifs would be the way to go

[martindwilson]

=SUMPRODUCT(--('hdcore1 VBA'!A1:A800=C2);--('hdcore1 VBA'!B1:B800="valid"))+SUMPRODUCT(--('hdcore2 VBA'!A1:A800=C2);--('hdcore2 VBA'!B1:B800="valid"))+SUMPRODUCT(--('hdcore3 NON-VBA'!A1:A800=C2);--('hdcore3 NON-VBA'!B1:B800="valid"))+SUMPRODUCT(--('hdcore4 NON-VBA'!A1:A800=C2);--('hdcore4 NON-VBA'!B1:B800="valid"))
where c2 holds the name of interest

[scottylad2]

For countifs

COUNTIFS(Sheet1$A$1:$A$8000,"bobby",E1:E8000,"valid")+COUNTIFS(Sheet2!A1:A8000,"bobby",Sheet2!$E$1:$E$8000,"valid") etc through your 4 sheets.

Remember your ranges need to be the same size for sumproduct

[xatomicx]

Sumproduct i got a "can't find in the library" issue, i probably need to install something for it to work.

The countif seems to be logical, but can't make it work, let me tweak it a bit, i'll try to find out what doesnt work, i'll try it on a single sheet, if it works, i'll add the + for the other sheets.

=COUNTIF(hdcore1 VBA$A$1:$A$8000,"Benoit Johanne",E1:E8000,"valid")

this is a real example on a single sheet

doesnt work!

[scottylad2]

Sumproduct works in all versions i've used, can you upload a sample of your sheet? Again, which version of Excel are you using? if it's 2007 or later then countifs <<<< note the s on the end >>>> is probably the neatest way to go. The logic in sumproduct the way it's being suggested to you is doing the same as the countif but as you have more than one criteria it's doing the same as the countifs

[martindwilson]

its countifs not countif and will only work in excel 2007 or later
the sum product works must be a typo as its availiable in all versions probably a sheet name or something

[xatomicx]

i'm on 2003, that might be why i have some error message
Let me find out with somproduct, i i will also upload a sample of my sheet, let me just clear some info out, there is some private corporate info on those, i'll clear one out and will post it.

[martindwilson]

the one i uploaded is based on your sheet names

[xatomicx]

thanks to your sumproduct sheet !! it worked! i am very grateful to you guys, you saved me so many hours of work :D

Thanks again!