Counting values from other sheets with variables

fapb48

Board Regular
Joined
Sep 13, 2020
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I have two sheets. One is the master sheet with all the data. And the other is a sheet that i am pulling the totals from each specific value from.

On the master sheet i have a cell that acts like a week number.

I have many rows each one stating a week number and then i have enter a specific value on another column.
The total row calculates all values entered on that column but it changes according to the week number.

All i want to do is pull that data to another sheet. but instead of calculating all values for that specific week i want to calculate the total of each different value for that week.

I have attached a screenshot of my master sheet to see if it makes more sense.

On the green cell if i change to 13 it will count all values that were added on week 13.

I want to the same on the other sheet. As soon as i change the week number it pulls only the info for that week. But then again i want only the values for that specific week only

This is what i have tried but without success

B1 on my sheet is where i type the week number

=IF($B$1='Values'!$G$2,COUNTIF('Values'!J4:J,"SV9))

Because otherwise it will count all values with SV9 on that column and i have over a 1000 rows. and i dont to count all of those rows

Im sorry if i cant explain any better but hope this makes sense
 

Attachments

  • Untitled1.png
    Untitled1.png
    31.5 KB · Views: 6

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
=countifs(Range with week number in values sheet, B1 , Range with SV9 in values sheet, "SV9" )
 
Upvote 0
Beautiful. thank you very much.

Last question is.. How do i make the formula to count in a specific manner.

For example SV9F needs to count as "1" but SV9H needs to count as"0.5"
 
Upvote 0
I have tried this but it gives me an error return

=COUNTIFS(('DOX2'!G4:$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 F")+0.5*COUNTIFS('DOX2'!G4:$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 H"))
 
Upvote 0
you have a 2nd thread running
which seems to be the same question

Anyway - seems you have closed that one down
DOX2'!G4:$G
should be a range
DOX2'!G4:$G100
or
just the column
DOX2'!$G:$G

why does it not work ?
 
Last edited:
Upvote 0
change the brackets
=COUNTIFS('DOX2'!G4:$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 F")+(0.5*COUNTIFS('DOX2'!G4:$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 H"))
make sure
=COUNTIFS('DOX2'!G4:$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 F") works with no error
and same for
=(0.5*COUNTIFS('DOX2'!G4:$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 H"))
BUT needs a range
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top