Countifs Formula Query

grazza2a

New Member
Joined
Feb 7, 2017
Messages
4
Hi there and thanks in advance for any support provided.

I have a spreadsheet that captures the total number and type of requests a manager can give to an admin person. Usually, one request equals one task. However, on occasion, a single request can involve multiple instances of the same task. I can use a Countifs formula to calculate how many times a certain manager makes a certain request - and this works fine if we assume a single task per request. However, I can't figure out how to calculate when more than one task is completed per request?

By way of example I'm using this formula: =SUM(COUNTIFS($B$2:$B$26,"CEO",$C2:$C$26,{"Arrange Meeting"})) - adapted to cover all manager grades and all tasks.

A1 to D26 is the raw data. F1 to J5 are the calculations. This is an example spreadsheet only. The real version is much more complicated and with more data. See screenshot to see how it's being applied. How do I amend the formula to multiply the instance of the task by the number in column D if greater than 1? For instance; the Supervisor has requested photocopy 5 times but the task total in J5 should equal 37.

I'm sure it's very simple but has foxed me. I hope this makes sense and thanks again.
Admin Task Example.JPG
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
How about
=sumifs(D:D,B:B,$F2,C:C,G$1)
 
Upvote 0
Hi Fluff, I'm blown away by the simplicity of that answer. I need to work out how to apply it to the real workbook with data split over a number of spreadsheets - but that certainly works. Thank you so much.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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